Search code examples
sql-serverunionsap-asesql-cte

Reading multiple records related to every record in the result set at a time

I have 3 tables:

  • Message table having MsgID, MsgText, MsgDate columns. MsgId is PK.
  • MessageSender table having MsgId, SenderId, SenderName. MsgId is FK.
  • MessageTo table having MsgId, ToId, ToName. MsgId is FK.

Every Message will have one Sender. But it can have many Recipients. i.e. For every record in the Message table, there will be one record in MessageSender table and more than one records in the MessageTo table.

I want to get ALL details of the all Messages in one query or at a time. e.g. for a specific Message, who is the Sender and who ALL are recipients.

How can I do this?

I am using MSSQL Server 2005 and Sybase 15.

Note: I have given only relevant details of the tables here. And I cannot change table schema since it's been there in production for a long time.

Solution 
  • select distinct M.MsgID, M.MsgText, MS.SenderID, MS.SenderName, 
MT.ToName, MT.ToId 
from Message M, MessageSender MS, MessageTo MT
where M.MessageID = MS.MessageID and 
M.MessageID = MT.MessageID 
and MsgDate like '%20130307%' -- To get the messages sent on 07-Mar-2013