Help with 2 tier selection - 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 2005 Forums

Transact-SQL (2005)

Help with 2 tier selection

Author

Topic

MSwanston
Starting Member

23 Posts

Posted - 2009-11-04 : 09:03:14

Hi
I have a current SQL selection which returns a list of data for all records where the logged-in user is associated with the required data. This is done via an INNER JOIN, and is a relatively simple selection. The code is as follows:


SELECT OS.SchemeID, OS.SchemeName FROM Schemes AS OS 
INNER JOIN dbo.SchemeAssistants ON OS.SchemeID = dbo.SchemeAssistants.SchemeID 
WHERE (dbo.SchemeAssistants.Assistant = @Assistant) 
AND (OS.DateDeleted IS NULL) ORDER BY OS.SchemeName

Now, they have updated the requirement to create a list of the data returned above, and then follow it with everything else, so no longer limiting it to only associated data.
My guess is that I can create a list of the above data, and then a list of everything else that is not in the first list (using the SchemeID field). My question is can this be done in one query, and if so, how do I order I correctly, or if not, how do I append one selection to another?
Please ask if this doesn't make sense or you need more information.
Thanks in advance
Martin

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-04 : 09:22:14

try LEFT JOIN instead of INNER JOIN but that gives problems with ORDER BY.
If you want ORDER BY OS.SchemeName and after that all the not associated data you use UNION


SELECT OS.SchemeID, OS.SchemeName FROM Schemes AS OS 
INNER JOIN dbo.SchemeAssistants ON OS.SchemeID = dbo.SchemeAssistants.SchemeID 
WHERE (dbo.SchemeAssistants.Assistant = @Assistant) 
AND (OS.DateDeleted IS NULL) ORDER BY OS.SchemeName
UNION ALL
SELECT OS.SchemeID, OS.SchemeName FROM Schemes AS OS 
LEFT JOIN dbo.SchemeAssistants ON OS.SchemeID = dbo.SchemeAssistants.SchemeID 
WHERE (OS.DateDeleted IS NULL) 
AND dbo.SchemeAssistants.SchemeID IS NULL

No, you're never too old to Yak'n'Roll if you're too young to die.

MSwanston
Starting Member

23 Posts

Posted - 2009-11-04 : 11:40:13

Hi
Thanks for that - it didn't work quite as I anticipated, but it got me on the right road to a solution - this is what I ended up using (if anyone thinks there is a better/easier way to achieve this, please shout)


SELECT OS.SchemeID, OS.SchemeName INTO #TempList1
FROM Schemes AS OS 
INNER JOIN dbo.SchemeAssistants ON OS.SchemeID = dbo.SchemeAssistants.SchemeID 
WHERE dbo.SchemeAssistants.Assistant = @Assistant
AND OS.DateDeleted IS NULL
ORDER BY OS.SchemeName
SELECT DISTINCT OS.SchemeID, OS.SchemeName INTO #TempList2
FROM Schemes AS OS 
INNER JOIN dbo.SchemeAssistants ON OS.SchemeID = dbo.SchemeAssistants.SchemeID
WHERE OS.SchemeID NOT IN (SELECT SIDL.SchemeID FROM Schemes AS SIDL 
INNER JOIN dbo.SchemeAssistants ON SIDL.SchemeID = dbo.SchemeAssistants.SchemeID 
WHERE dbo.SchemeAssistants.Assistant = @Assistant
AND SIDL.DateDeleted IS NULL)
ORDER BY OS.SchemeName
SELECT * FROM #TempList1 UNION ALL SELECT * FROM #TempList2
DROP TABLE #TempList1, #TempList2

Thanks
Martin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources