Santana Santana - 5 months ago 10
MySQL Question

Mysql query with multiple join

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.

Answer

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.

Comments