joining three tables in mysql

Question

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

Can you post some of the things you tried? (You can edit the question to add them, don't try to squeeze them into comments.) It seems you know that `JOIN` is the key word here, so maybe we can fill in a gap in your understanding of how that works? — IMSoP, Sep 07 '13 at 13:06
Please show your SELECT statements & results. This will stop down votes. — david strachan, Sep 07 '13 at 13:06

O. Jones · Accepted Answer · 2013-09-07T13:29:21.793

5

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.

edited Sep 07 '13 at 13:29

answered Sep 07 '13 at 13:07

O. Jones

103,626
17
118
172

I'm pretty sure there's no need for sub-queries here, they just complicate the query. – IMSoP Sep 07 '13 at 13:09
As all the tables have a common field, wouldn't a simple join be more efficient than subqueries on each table? – Fluffeh Sep 07 '13 at 13:10
A three way unsummarized join will yield lots of extra rows when there's more than one item for each code in either table-b or table-c. – O. Jones Sep 07 '13 at 13:11
1

Ah, right, I think I get where you're coming from. You should try to explain that in the answer, though, as it may be what the OP was struggling with in the first place. – IMSoP Sep 07 '13 at 13:12
Brilliant. Until you laid out the ungrouped table like that it hadn't even occurred to me that it would be effectively a `CROSS JOIN` between the B and C tables. :) – IMSoP Sep 07 '13 at 13:23
This is fine working well... Thanks – mansoondreamz Sep 07 '13 at 15:15

joining three tables in mysql

1 Answers1