I have the following query, but it returns a row for each time UserCodeBlocked is not equal to UserCodeSender...
What I need is just to select all messages for a user EXCEPT when they are from a user in the blocklist...
I have tried the keyworddistinct but that ofcourse doesnt work since Row_number makes every row unique...
tblMessages contains the actual message
tblUsersandmessages relates users and messages
select ROW_NUMBER() OVER (ORDER BY a.SentDateTime DESC) as RowNum,a.MessageID,a.UserCodeSender,a.MessageTitle,a.SentDateTime
FROM tblMessages a
INNER JOIN tblUsersAndMessages b ON (a.MessageID=b.MessageID)
INNER JOIN tblUserData c ON (a.UserCodeSender=c.UserCode)
RIGHT JOIN tblBlockList bl ON bl.UserCodeBlocked<>a.UserCodeSender
WHERE b.UserCode=5
did you try a left join instead of a right join? You want the data for the message, not for the block list.
select ROW_NUMBER()OVER (ORDER BY a.SentDateTimeDESC)as RowNum,a.MessageID,a.UserCodeSender,a.MessageTitle,a.SentDateTimeFROM tblMessages aINNERJOIN tblUsersAndMessages bON (a.MessageID=b.MessageID)INNERJOIN tblUserData cON (a.UserCodeSender=c.UserCode)LEFTOUTER JOIN tblBlockList blON bl.UserCodeBlocked<>a.UserCodeSenderWHERE b.UserCode=5
If this doesn't help, please post the table structures and/or relationship diagram
|||tblBlockListUserCodeBlocker int 'person who has blocked someone
UserCodeBlocked int 'person being blocked
tblMessages
UserCodeSender int
MessageID int
MessageTitle
...
tblUsersAndMessages
UserCode int
MessageID int
...sql
