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

I can't quite follow what you want. Could you do a few additional things:

1. Turn your table structures into DDL and DML, so test SQL Statements can be written

2. Put out the results you want to see exactly. That would be a big help.

Thanks

|||CREATE TABLE [dbo].[UserMessages](
[MessageID] [int] IDENTITY(1,1) NOT NULL,
[ToUserID] [int] NOT NULL,
[FromUserID] [int] NOT NULL,
[Subject] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Message] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[isNew] [bit] NOT NULL CONSTRAINT [DF_UserMessages_isNew] DEFAULT ((1)),
[DateSent] [datetime] NOT NULL CONSTRAINT [DF_UserMessages_DateSent] DEFAULT (getdate()),
CONSTRAINT [PK_UserMessages] PRIMARY KEY CLUSTERED
(
[MessageID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[UserMessagesReplies](
[ReplyMessageID] [int] IDENTITY(1,1) NOT NULL,
[MessageID] [int] NOT NULL,
[ToUserID] [int] NOT NULL,
[FromUserID] [int] NOT NULL,
[Message] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[isNew] [bit] NOT NULL CONSTRAINT [DF_UserMessagesReplies_isNew] DEFAULT ((1)),
[DateSent] [datetime] NULL CONSTRAINT [DF_UserMessagesReplies_DateSent] DEFAULT (getdate()),
CONSTRAINT [PK_UserMessagesReplies] PRIMARY KEY CLUSTERED
(
[ReplyMessageID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Result Set:

MessageID | ToUserID | FromUserID
-
1 1 2
2 2 1
3 1 4
4 1 5

MessageID=2 should appear because there is a response (ReplyMessageID=6) ToUserID=1

Select From UserMessages Where ToUserID=1

Thank you!
|||

It looks like you need to union the first table with the second table:

create view messagesTogether

as

select messageId, toUserId, fromUserId

from userMessages

union

select replyMessageId, toUserId, fromUserId

from userMessages

then get it where the toUserId is the same

But MessageId and ReplyMessageId don't match. I am uncomfortable with these table structures, so this is probably my best current guess. Am I close, does this make sense?