A Query I've Encounter That Has Me Stumped ... Anybody?

Hello Everyone,

I was hoping somebody could help me out with a query I've been trying to solve.


My Table Structure:

[UserMessages]

MessageID int
ToUserID int
FromUserID int
Subject varchar(200)
Message varchar(max)
isNew bit


[UserMessageReplies]

ReplyMessageID int
MessageID int
ToUserID int
FromUserID int
Message varchar(max)
isNew bit


Sample Data:

[UserMessages]

MessageID | ToUserID | FromUserID | Subject | Message | isNew
-------------------------------------------------------------------------------------------------------------
1 1 2 test subject Message Body 0
2 2 1 test subject Message Body 0
3 1 4 test subject Message Body 1
4 1 5 test subject Message Body 1


[UserMessageReplies]


ReplyMessageID | MessageID | ToUserID | FromUserID | Message | isNew
---------------------------------------------------------------------------------------------------------------------------
1 1 2 1 re: Message Body 0
2 1 1 2 re: Message Body 0
3 1 2 1 re: Message Body 0
4 1 1 2 re: Message Body 0
5 1 2 1 re: Message Body 1
6 2 1 2 re: Message Body 1


Explanation:

ReplyMessageID = 1-4 signfies that there is a thread response to MessageID 1 but they have been opened (isNew =0).
ReplyMessageID = 5 signfies that there is a new thread response to MessageID 1.
ReplyMessageID = 6 signifies that there is a reply from a message UserID=1 sent out (MessageID 2)

There needs to be a filter to check UserMessages.TOUserID=1 OR UserMessageReplies.ToUserID=1 to ensure that we can capture a msg that UserID=1 had sent out but now there is a reply to the Message by the User (in this example data i've setup MessageID = 2 to handle that possibility)

My Goal:

To select all messages for a ToUserID and order them by UserMessages.isNew or UserMessageReplies.isNew. This must include a message the user has sent but now has received a reply. (This means the first filter; UserMessages.ToUserID, must be overwritten with UserMessageReplies.ToUserID)



Thanks very much!

Chris