I have 3 tables. table A has two fields code and item. Table B has 3 fields code, qty_recd and recd_dt. Table C has 3 fields Code, qty_sold, sold_dt. I want to get a result in this way:
For each record in Table A, sum of qty_recd from Table B and sum of qty_sold from Table C.
TABLE-A
code Item
----- ------
A1 AMMONIA SOLUTION
TABLE-B
code qty_recd recd_dt
----- -------- -------
A1 5 2013-06-01
A1 8 2013-04-18
A1 3 2013-05-22
TABLE-C
code qty_sold sold_dt
----- -------- -------
A1 1 2013-08-10
A1 4 2013-09-01
A1 2 2013-05-11
I want result like this
code item tot_recd tot_sold
----- -------- ------- --------
A1 AMMONIA SOLUTION 16 6
I tried several constructs, but not getting desired output. Please help to construct SELECT statement
You want to join your master product list to two summaries. (Don't use hyphens in table names routinely!)
One of the summaries is:
SELECT code, SUM(qty_recd) AS qty_recd
FROM `TABLE-B`
GROUP BY code
You can guess the other.
Your overall query should be
SELECT a.code, a.item, b.qty_recd, c.qty_sold
FROM `TABLE-A` AS a
LEFT JOIN (
SELECT code, SUM(qty_recd) AS qty_recd
FROM `TABLE-B`
GROUP BY code
) AS b ON a.code = b.code
LEFT JOIN (
SELECT code, SUM(qty_sold) AS qty_sold
FROM `TABLE-C`
GROUP BY code
) AS c ON a.code = c.code
Here's a SQL Fiddle showing it. http://sqlfiddle.com/#!2/f6cb7/1/0
You might naively just JOIN all three tables and then use GROUP BY. But then you'd be summarizing this nasty cross-product derived table:
CODE qty_recd qty_sold
A1 5 1
A1 5 4
A1 5 2
A1 8 1
A1 8 4
A1 8 2
A1 3 1
A1 3 4
A1 3 2
For example, this incorrect query would do that.
SELECT a.code, a.item, SUM(b.qty_recd), SUM(c.qty_sold) //WRONG!
FROM `TABLE-A` AS a
LEFT JOIN `TABLE-B` AS b ON a.code = b.code
LEFT JOIN `TABLE-C` AS C ON a.code = c.code
GROUP BY a.code, a.item
See this fiddle. http://sqlfiddle.com/#!2/f6cb7/3/0
That's not what you want. Hence the need for summary subqueries.