How to join two tables in SQL and get only 1 row per joining index

Question

If I have 3 tables like the following:

Table A:
Item Start_QTY
A    100
B    100

Table B:

Item Purchase_QTY
A    10
C    20

Table C:

Item End_QTY
A    90
B    10
C    10

How do I join the 2 tables to get the following result:

Item Start_QTY  Purchase_QTY  End_QTY
A    100  10 90
B    100  NULL 10
C    NULL 20 10

If I do the following:

Select COALESCE(a.item, b.item, c.item) as item, a.start_QTY, b.purchase_QTY, c.End_QTY from 
A as a 
full outer join 
B as b
on a.item = b.item
full outer join
C as c
on a.item = c.item

I get the following:

A 100 10 90
B 100 NULL 10
C NULL 20 NULL
C NULL NULL 10

What do you mean with "get only 1 row per joining index"? – jarlh Dec 29 '14 at 13:17 — jarlh, Dec 29 '14 at 13:17
What database are you using? – Gordon Linoff Dec 29 '14 at 13:19 — Gordon Linoff, Dec 29 '14 at 13:19
MSSQL, But I want to to it both in MSSQL and MYSQL – calvin12 Dec 29 '14 at 13:20 — calvin12, Dec 29 '14 at 13:20

Saharsh Shah · Answer 1 · 2014-12-30T05:41:51.740

4

Use FULL OUTER JOIN to fetch all the data from both tables

Try this:

SELECT COALESCE(A.Item, B.Item) AS Item, A.QTY, B.Price
FROM TableA A 
FULL OUTER JOIN TableB B ON A.Item = B.Item;

::EDIT::

SELECT COALESCE(C.Item, A.Item, B.Item) AS Item, A.Start_QTY, B.Purchase_QTY, C.End_QTY
FROM TableC C  
FULL OUTER JOIN TableA A ON C.Item = A.Item
FULL OUTER JOIN TableB B ON C.Item = B.Item;

edited Dec 30 '14 at 05:41

answered Dec 29 '14 at 13:15

Saharsh Shah

28,687
8
48
83

wont this return A 100 10 B 100 NULL NULL NULL 20 ? – calvin12 Dec 29 '14 at 13:18
I have updated my question to show you exactly what I am doing and what is being returned – calvin12 Dec 29 '14 at 13:42

score 1 · Answer 2 · answered Dec 29 '14 at 13:22

If you want it to work in both SQL Server and MySQL, then you cannot use full outer join. The following may do what you want:

select item, max(qty) as qty, max(price) as price
from (select item, qty, null as price
      from tablea 
      union all
      select item, null, price
      from tableb
     ) ab
group by item;

In SQL Server alone, you should use full outer join.

How to join two tables in SQL and get only 1 row per joining index

2 Answers2