Need help in joinning tables. - 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)

Need help in joinning tables.

Author

Topic

alienworkshop
Starting Member

2 Posts

Posted - 2006-06-13 : 11:54:28

Hi all,

I have the following tables.
[Messages] (Msg_ID, [TimeStamp], Subject, Content)

[Recipients] (Rec_ID, Msg_ID, Sender_ID, Recipient_ID, UnReadFlag)

I want to get the items sent by a user.. where a user might have sent the same message to lots of other people as CC so when you normally join the tables.. it shows 5 records if you have sent one message to 5 different users. I need to get only one record per message...

here is the content of my stored procedure.

Select DISTINCT
Messages.Msg_Id As MsgID,
Recipients.Sender_ID As Sender,
MemSender.Member_Company as SenderCompany,
Recipients.Recipient_ID as Recipient,
MemRec.Member_Company as RecCompany,
Messages.Subject as Subject,
Recipients.UnReadFlag,
Messages.[TimeStamp] as TimeStamp,
Messages.Content,
DATEDIFF ( day , Messages.[TimeStamp] , getdate()) as SentBeforeDays
from
Messages
join Recipients on Messages.Msg_ID = Recipients.Msg_ID
join Members MemSender on Recipients.Sender_id = MemSender.Member_id
join Members MemRec on Recipients.Recipient_ID = MemRec.Member_id
where
Recipients.Sender_ID = '' + @member_id + ''
and DATEDIFF ( day , Messages.[TimeStamp] , getdate()) <= 4

order by [timestamp] desc

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-13 : 19:37:22

One record of what per message? You are selecting a DISTINCT for all these fields

Messages.Msg_Id As MsgID,
Recipients.Sender_ID As Sender,
MemSender.Member_Company as SenderCompany, 
Recipients.Recipient_ID as Recipient, 
MemRec.Member_Company as RecCompany,
Messages.Subject as Subject, 
Recipients.UnReadFlag, 
Messages.[TimeStamp] as TimeStamp, 
Messages.Content,
DATEDIFF ( day , Messages.[TimeStamp] , getdate()) as SentBeforeDays

That is what is causing all rows. Narrow down the selected fields or rewrite your logic.

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-14 : 03:03:33

Refer point 2
http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx

Madhivanan

Failing to plan is Planning to fail

alienworkshop
Starting Member

2 Posts

Posted - 2006-06-14 : 04:17:08

Hi,

here is the database structure once again.. and my previous message contains the query that i am using..

[Messages] (Msg_ID, [TimeStamp], Subject, Content)

[Recipients] (Rec_ID, Msg_ID, Sender_ID, Recipient_ID, UnReadFlag)

this is a database driven email simulation.
when i send a message to 5 people, there is one record inserted in the Messages table and 5 records are inserted in the Recipients table.
Now consider the scenario where i only need to show my "Sent Items"
means to say, when i go to the sent items view.. it shows me the messages i have sent.. no matter how many Recipients where there.. i will only be shown one record.. from the Messages table.. the concept is same as the one used in normal email inboxes. for sent items.. the only problem is if i send a message to more than one person.. (as CC) then i find more than one entries in my sent items.. which is not what i want .. i need only one entry from the messages table.. irrespective of how many recipients are there.. because thats the logic followed when you are showing only the sent items.

one possible solution that i can see is to insert the Sender_ID column in the messages table to make it easy to identify who sent this message.. and no need to join with the recipients table.. i need your suggestions..

Thanks in advance,

Alienworkshop

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-14 : 05:01:43

Use

SELECT COUNT(*) FROM Messages WHERE Msg_ID IN (SELECT Msg_ID FROM Recipients WHERE Sender_ID = @MySenderID)

Strange logic to have sender in Recipients table. I suggest moving Sender_ID from Recipients table to Messages table, for normalization.

To get a complete list of all senders at once, use

SELECT		s.Sender_ID,
		COUNT(*) AS 'Messages sent' 
FROM		(
			SELECT	DISTINCT Sender_ID,
				Msg_ID
			FROM	Recipients
		) s
INNER JOIN	Messages ON Messages.Msg_ID = s.Msg_ID
GROUP BY	s.Sender_ID

This is the simplest way I know for your table layout

SELECT		Sender_ID,
		COUNT(DISTINCT Msg_ID)
FROM		Recipients
GROUP BY 	Sender_ID

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources