Mysql query with multiple join

Question

I have the following tables and would like to get the result as follow

Table po
ID | Date
1  | 20-Jun-2016

Table podetails
ID | poid | itemcode | quantity
1  | 1    | SOAP123  | 100

Table poreceived
ID | poid | itemcode | quantity
1  | 1    | SOAP123  | 20
2  | 1    | SOAP123  | 60

Result should be:

PO | Date        | itemcode | quantity
1  | 20-Jun-2016 | SOAP123  | 80

What I have done is:

SELECT 
    po.id, podetails.itemcode, poreceived.quantity
FROM
    po
        LEFT JOIN
    podetails ON podetails.poid = po.id
        LEFT JOIN
    poreceived ON poreceived.poid = podetails.poid

But the result is not what I expected.

Any help would be appreciate it.

When results are unexpected, it would be useful to show them or to explain in what ways they are unexpected. — rrauenza
– rrauenza, Commented Jun 20, 2016 at 5:37

Tim Biegeleisen · Accepted Answer · 2016-06-20 06:15:58Z

2

It looks like you need to do an aggregation of the poreceived table by poid. You can use a subquery for this:

SELECT po.id, COALESCE(podetails.itemcode, 'NA'), COALESCE(t.quantity, 0)
FROM po
LEFT JOIN podetails
    ON podetails.poid = po.id
LEFT JOIN
(
    SELECT poid, itemcode, SUM(quantity) AS quantity
    FROM poreceived
    GROUP BY poid, itemcode
) t
    ON t.poid = podetails.poid AND t.itemcode = podetails.itemcode

Other than the aggregation problem, your query strategy looked correct. I also added COALESCE to the columns from the podetails and poreceived tables in case the id from po does not match to anything.

edited Jun 20, 2016 at 6:15

answered Jun 20, 2016 at 5:35

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Santana Over a year ago

Thanks Tim. But when you have another row in podetails with the same poid number with different itemcode, then the quantity result is not correct for each item. Any idea?

Tim Biegeleisen Over a year ago

@Santana I updated my query to include the itemcode in the join condition.

Thorsten Kettner Over a year ago

Good answer, although I find COALESCE inappropriate here (converting quantity to string, only to replace NULL with '' which even look identical in many tools).

Tim Biegeleisen Over a year ago

@ThorstenKettner Thanks for the comments.

Collectives™ on Stack Overflow

Mysql query with multiple join

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related