Chuckie Sullivan Chuckie Sullivan - 4 months ago 9
SQL Question

Joining two simple tables using nulls

I am trying to join two tables that look like this:

Table A
ID X
1 0
1 8

Table B
ID Y
1 0
1 24
1 48


Into something that looks like this:

Table D
ID X Y
1 0 0
1 8 24
1 NULL 48


Or even this will work:

Table C
ID X Y
1 0 NULL
1 8 NULL
1 NULL 0
1 NULL 24
1 NULL 48


But this:

SELECT a.ID, X, Y FROM [Table A] a
LEFT JOIN [Table] b ON a.ID = b.ID
WHERE a.ID = 1
GROUP BY a.ID, X, Y


Keeps giving me this:

Table X
ID X Y
1 0 0
1 0 24
1 0 48
1 8 24
1 8 48

Answer

this will give the solution with nulls:

SELECT ID, X, null as Y FROM "Table A"
UNION ALL
SELECT ID, null X, Y FROM "Table B"

I hope it helps.