how do you do this merge? - 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

Transact-SQL (2000)

how do you do this merge?

Author

Topic

Zim327
Yak Posting Veteran

62 Posts

Posted - 2004-11-12 : 16:49:57

Table: PurchaseOrders
Key PO
1 1000
3 3000
5 5000

Table: Accounts
Key AC
2 200
4 400
5 500

Result I want (underscores mean blanks):
Key PO AC
1 1000 ___
2 ____ 200
3 3000 ___
4 ____ 400
5 5000 500

the best I could come up with is this
select AC.id, AC.AC, PO.PO from AC
left Join PO on PO.id = AC.id
union
select PO.id, PO.PO, AC.AC from PO
left join AC on AC.id = PO.id

but it's clunky...
is there a better way??

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-12 : 17:06:41

try this:

select
	coalesce(PO.id,AC.id) as [Key],
	coalesce(PO.PO,'____') as PO,
	coalesce(AC.AC,'____') as AC
from
	PurchaseOrders PO
	full join Accounts AC on PO.id = AC.id
order by 1

rockmoose

Zim327
Yak Posting Veteran

62 Posts

Posted - 2004-11-12 : 18:12:37

Hey thanks for the reply,
I won't be able to try it until monday
I'll let you know then...

Thanks again,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources