Multiple Full Joins

Question

Post reply

Multiple Full Joins

deepa.devadas

Valued Member

Points: 55
More actions
July 21, 2011 at 9:35 am

#244691

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

-- Naming a table [Index] is NOT advisable.

SELECT ad.ACCOUNTID, x.SecurityID,

MatchStatus = CASE WHEN a.ACCOUNTID IS NULL THEN 'Only in Index' ELSE 'Matched' END

FROM [INDEX] x

CROSS JOIN (SELECT DISTINCT ACCOUNTID FROM ACCOUNTS) ad

LEFT JOIN ACCOUNTS a

ON a.ACCOUNTID = ad.ACCOUNTID AND a.SecurityID = x.SecurityID

ORDER BY ad.ACCOUNTID, x.SecurityID

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

deepa.devadas Valued Member Points: 55 More actions · Answer 2

Awesome!!!. This is exactly what I want

Thanks a lot.

rgtft Hall of Fame Points: 3518 More actions · Answer 3

Chris,

Wouldn't your SQL miss the case:

Acct2 Sec77 Only in Account

I was playing around with this; that's the only reason I ask.

deepa.devadas Valued Member Points: 55 More actions · Answer 4

If you use a FULL JOIN instead of the LEFT JOIN, it should include even 'Only in Account' row.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

rgtft (7/21/2011)
Chris,
Wouldn't your SQL miss the case:
Acct2 Sec77 Only in Account
I was playing around with this; that's the only reason I ask.

You're absolutely right, good spot.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden