Query help 1

[jrprogr]

I have a written a query which is not producing the desired output.Please help me in getting the output.

select b.id,emp,city,gender,age from tablea a full outer join tableb b on a.id=b.id


Tablea
------

id emp city
------------ -------------------- ----------
12345 test1 NY
12346 test2 VA
12347 test3 MD
12348 test4 GA
12350 test5 LA



Tableb
------


id gender age
------------ ------ ----
12346 M 20
12347 M 25
12348 F 30
12349 M 56
12364 F 58


I need the below output.


id emp city gender age
------------ -------------------- ---------- ------ ----
12345 test1 NY NULL NULL
12346 test2 VA M 20
12347 test3 MD M 25
12348 test4 GA F 30
12350 test5 LA NULL NULL
12364 NULL NULL F 58
12349 NULL NULL M 56

 

[AlexCuse]

You need to first use a union to get all the ID's, then left join to both tables. How about this?

select z.id, a.emp, a.city, b.gender, b.age
from
(
select id from tableA
union select id from tableB
) z
left join
tableA a
on z.id = a.id
left join
tableB b
on z.id = b.id

(not tested)

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[jrprogr]

Thanks alex!!
yr query works fine..

How about below query which also works but do yu see any problems with the below query

select id = coalesce(a.id, b.id), a.emp, a.city, b.gender, b.age
from tablea a full outer join tableb b
on a.id = b.id

 

[chris123321]

very cool..

 

[AlexCuse]

jrprogr - I have not had to work too much with full outer joins, so I don't know if it will work in all cases, but it certainly seems to work OK with test data. Looking at the execution plan, it seems that it will be a better performer too. Good one (and nice use of coalesce )

Ignorance of certain subjects is a great part of wisdom