Query Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Query Help

Author

Topic

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-04-27 : 23:11:10

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-28 : 00:09:22

use FULL OUTER JOIN


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

sqldba2k6
Posting Yak Master

176 Posts

Posted - 2007-04-28 : 09:36:09

Thanks Khtan!!
yr query works fine..

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

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-28 : 10:03:59

performance ! You can run both query and check the execution plan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources