Join 2 tables - all records for both

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

Transact-SQL (2000)

Join 2 tables - all records for both

Author

Topic

JP_xpto
Starting Member

10 Posts

Posted - 2002-11-08 : 06:10:52

Please consider this 2 tables:

TAB01
----------------
Account1 Value1
----------------
5551 * 60
5552 * 80
5554 * 4

TAB02
--------------------------
Account2 Value2 Cod
--------------------------
5551 * 62 * bv
5553 * 1 * vp

The objective is to get a view like this:

Accounts Value1 Value2
--------------------------
5551 * 60 * 62
5552 * 80 * null
5553 * null * 1
5554 * 4 * null

I cant get this done ...any ideias?

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-11-08 : 06:16:22

SELECT COALESCE(Account1, Account2) AS Accounts, Value1, Value2
FROM TAB01
FULL OUTER JOIN TAB02 ON Account1 = Account2

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-11-08 : 06:21:38

select c.account1, d.value1, e.value2
from (select a.account1 from table1 a
union
select b.account2 from table2 b) c
left join table1 d on d.account1 = c.account1
left join table2 e on e.account2 = c.account1
order by 1

???

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources