help on query

I need to select all messages for user X from all users, except those who user X has blocked in tblBlockList.
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

|||tblBlockList
UserCodeBlocker int 'person who has blocked someone
UserCodeBlocked int 'person being blocked

tblMessages
UserCodeSender int
MessageID int
MessageTitle
...

tblUsersAndMessages
UserCode int
MessageID int
...sql