Friday, January 25, 2008

Nested T-SQL to Select Everything but a Subset of Data

The Basic idea behind the following query is to NOT select the messageIDs that are assigned a TagID of '2' (archived) and that are also for the predetermined UserNameReceiving. Take note the use of the nested Select Statements as opposed to using a JOIN. I find this to be clearer and more straight forward.

SELECT DISTINCT MessageID, UserNameReceiving, UserNameSending, Subject, Message, Date
FROM Messages
WHERE (UserNameReceiving = @UserNameReceiving)
AND (NOT (MessageID IN
(SELECT MessageID
FROM TaggedMessages AS TaggedMessages_1
WHERE (TagID = '2'))))
ORDER BY Date DESC

No comments: