Multiple Full Joins - 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 2008 Forums

Transact-SQL (2008)

Multiple Full Joins

Author

Topic

ddevadas
Starting Member

2 Posts

Posted - 2011-07-21 : 11:30:23

Hi

I have 2 tables:
"Accounts" Table
AccountId SecurityId
Acct1 Sec1
Acct1 Sec2
Acct2 Sec3
Acct2 Sec2
Acct2 Sec1
Acct2 Sec77

"Index" Table
SecurityId
Sec1
Sec2
Sec3
Sec4
Sec5

I want the Index Table to be full joined to each of accounts in the "Accounts" Table. The final dataset should look like:
AccountId SecurityId MatchStatus
Acct1 Sec1 Matched
Acct1 Sec2 Matched
Acct1 Sec3 Only in Index
Acct1 Sec4 Only in Index
Acct1 Sec5 Only in Index
Acct2 Sec1 Matched
Acct2 Sec2 Matched
Acct2 Sec3 Matched
Acct2 Sec4 Only in Index
Acct2 Sec5 Only in Index
Acct2 Sec77 Only in Account

Could someone help me with this query.

Thanks

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-07-21 : 12:01:47

select a1.*, s.SecurityId, case when a1.AccountID is not null then 'matched' when s.type = 1 then 'only in Index' when type = 2 then 'only in Accounts' end
from (select distinct AccountID from Accounts) a1
cross join
(select distinct SecurityId, type = 1 from Index
union all
select distinct SecurityId, type = 2
from Accounts where SecurityId not in (select SecurityId from Index)
) s
left join Accounts a
on a.AccountID = = a1.AccountID
and a.SecurityId = s.SecurityId

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-07-21 : 12:09:19

or using a FULL JOIN:


WITH UAccounts
AS
(
	SELECT DISTINCT AccountId
	FROM Accounts
)
SELECT
	COALESCE(U.AccountId, A.AccountId) AS AccountId
	,COALESCE(I.SecurityId, A.SecurityId) AS SecurityId
	,CASE
		WHEN A.AccountId IS NULL
		THEN 'Only in Index'
		WHEN U.AccountId IS NULL
		THEN 'Only in Account'
		ELSE 'Matched'
	END AS MatchStatus
FROM UAccounts U
	CROSS JOIN [Index] I
	FULL JOIN Accounts A
		ON U.AccountId = A.AccountId
			AND I.SecurityId = A.SecurityId
ORDER BY AccountId, SecurityId

ddevadas
Starting Member

2 Posts

Posted - 2011-07-21 : 12:16:14

Awesome...This worked
Thanks a lot.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources