CODEWITHSUNDEEP
mysqlsqlleft-joininner-join
Itamar
Itamar

Reputation: 59

SQL Left Join on Multiple Tables With Different Values

I've got the following tables :

Table A :
itemCode   quantity
1           5
2           6
3           10

Table B :
itemCode   quantity
1           7
2           23
4           19

Table C:
itemCode   quantity
1           45
2           23
5           49

I'd like to have the table that looks as follows :

itemCode  A_Quantity B_Quantity C_Quantity
1            5          7          45
2            6          23         23
3            10         0          0
4            0          19         0
5            0          0          49

But using LEFT JOIN all I can manage to do is having a table that contains rows for itemCode 1,2 , which are the codes that are common to all tables.

Upvotes: 0

Views: 205

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

Use union all and aggregation:

select itemcode,
       max(a) as a, max(b) as b, max(c) as c
from ((select itemcode, quantity as a, 0 as b, 0 as c from a
      ) union all
      (select itemcode, 0 as a, quantity as b, 0 as c from b
      ) union all
      (select itemcode, 0 as a, 0 as b, quantity as c from c
      )
     ) abc
group by itemcode;

An alternative available in other databases is full outer join. However, MySQL does not support that type of join.

EDIT:

It you have an items table, then use left join:

select i.itemcode, a.quantity as a, b.quantity as b, c.quantity as c
from items i left join
     a
     on i.itemcode = a.itemcode left join
     b
     on i.itemcode = b.itemcode left join
     c
     on i.item_code = c.itemcode;

You can generate such a table using union if you really wanted to:

from (select itemcode from a union select itemcode from b union select itemcode from c
     i left join
     . . .

Upvotes: 1

Related Questions