CODEWITHSUNDEEP
sqlsql-server
Pat Migliaccio
Pat Migliaccio

Reputation: 1070

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

Upvotes: 0

Views: 42

Answers (3)

Stavr00
Stavr00

Reputation: 3314

ROWNUM and CTEs:

WITH 
ra AS
 (SELECT ROW_NUMBER() OVER (ORDER BY ID,X) AS row,id,x FROM a)
,rb AS
 (SELECT ROW_NUMBER() OVER (ORDER BY ID,Y) as row,id,y FROM b)

SELECT COALESCE(ra.id,rb.id),x,y
FROM ra
INNER JOIN rb
ON ra.id = rb.id AND ra.row = rb.row

Upvotes: 0

Massimiliano Carosi
Massimiliano Carosi

Reputation: 284

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.

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

Use union all.

select id,x,null y from tablea
union all
select id,null,y from tableb

or a full join.

select a.id,a.x,b.y 
from tablea a
full join tableb b on a.id = b.id

Upvotes: 1

Related Questions