select query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

select query

Author

Topic

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2004-06-14 : 02:35:25

Trying to do some thing like this:
sender (customerID int, msgid int)
receiver (customerId int, msgid int)

now we store the message sent by a customer in the sender with respect to the msgid
the table receiver stores the customers who have received a message i.e. the same msgid with the different customerId
for e.g. if a customer A sends message to customer B it would be saved as


sender 
customerid  msgid 
A		12101


Receiver

CustomerID  msgid
B		12101

now i want to find out how many senders have received messages from the receivers.
meaning if A has sent a message to B and whether B has sent a message back to A
got to find out how many such uses exists in sender table with a message from the receivers whom they had sent a message.

regards,
Harshal.

He is a fool for five minutes who asks ,
but who does not ask remains a fool for life!

http://www.sqldude.4t.com

gates_micro
Starting Member

29 Posts

Posted - 2004-06-14 : 03:44:14

when sender b sends msg back to a from reciever table a new id is generated or same id is used when sender a from sender table sent teh msg to reciever b in the reciever table.

gates_micro
Starting Member

29 Posts

Posted - 2004-06-14 : 03:45:22

quote:
Originally posted by gates_micro

when sender b sends msg back to a reciever table a new id is generated or same id is used when sender a from sender table sent teh msg to reciever b in the reciever table.

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2004-06-14 : 04:29:56

quote:
Originally posted by gates_micro

when sender b sends msg back to a from reciever table a new id is generated or same id is used when sender a from sender table sent teh msg to reciever b in the reciever table.

a new message id is generated in the sender table and a corresponding entry for that message in receiver table is stored so the tables become like:
[code]
sender
customerid msgid
A 12101
B 12102

Receiver

CustomerID msgid
B 12101
A 12102
[\code]

He is a fool for five minutes who asks ,
but who does not ask remains a fool for life!

http://www.sqldude.4t.com

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-06-14 : 04:43:25

Step 1...
Determine who has been sent a message, and by whom

SELECT R.ReceiverID, S.SenderID, S.MsgID
FROM Receiver R INNER JOIN Sender S ON S.MsgID=R.MsgID

This will give you a list of sender IDs, ReceiverIDs and the message ID that was sent.

Step 2..
Use that query as a derived table to work out of any of the Receivers are senders
SELECT OriginalMessage.ReceiverID, OriginalMessage.MsgID, OriginalMessage.ReceiverID
FROM (SELECT R.ReceiverID, S.SenderID, S.MsgID
FROM Receiver R INNER JOIN Sender S ON S.MsgID=R.MsgID) AS OriginalMessage
INNER JOIN (SELECT R.ReceiverID, S.SenderID, S.MsgID
FROM Receiver R INNER JOIN Sender S ON S.MsgID=R.MsgID) AS ReplyMessage
ON OrginalMessage.ReceiverID=ReplyMessage.SenderID

Get the idea ? The one problem I can see is that what happens if the message sent back to the original ID isn't a reply to the first message, but a new message sent some time later ? From the schema you have outlined you are not relating messages together as Original/Reply pairs.

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2004-06-14 : 05:08:46

quote:
Originally posted by JasonGoff

Step 1...
Determine who has been sent a message, and by whom

SELECT R.ReceiverID, S.SenderID, S.MsgID
FROM Receiver R INNER JOIN Sender S ON S.MsgID=R.MsgID

This will give you a list of sender IDs, ReceiverIDs and the message ID that was sent.

Step 2..
Use that query as a derived table to work out of any of the Receivers are senders
SELECT OriginalMessage.ReceiverID, OriginalMessage.MsgID, OriginalMessage.ReceiverID
FROM (SELECT R.ReceiverID, S.SenderID, S.MsgID
FROM Receiver R INNER JOIN Sender S ON S.MsgID=R.MsgID) AS OriginalMessage
INNER JOIN (SELECT R.ReceiverID, S.SenderID, S.MsgID
FROM Receiver R INNER JOIN Sender S ON S.MsgID=R.MsgID) AS ReplyMessage
ON OrginalMessage.ReceiverID=ReplyMessage.SenderID

Get the idea ? The one problem I can see is that what happens if the message sent back to the original ID isn't a reply to the first message, but a new message sent some time later ? From the schema you have outlined you are not relating messages together as Original/Reply pairs.

i'll try that.
that is not a problem i don't have to check whether it is a reply to the orignal message just got to check if there is a two way communication between the two users.

thanks for the help.
Regards,
Harshal.

He is a fool for five minutes who asks ,
but who does not ask remains a fool for life!

http://www.sqldude.4t.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources