help with query (select distinct where ...)

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 2005 Forums

Transact-SQL (2005)

help with query (select distinct where ...)

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-10-08 : 12:02:52

Hi,

I have a table as seen below. Its quite a large table but I was quite suprised when I ran the following query and good fast results.

select distinct(messageTo) from tblmessage where messagefrom =@userID

This gets a unique list of users that have been messaged by the user. I would like to try and modify this query so it retreives a distinct list of users the user has messaged, but only bringing back records where the user has replied. (which is = to sent a message to the user as well)

Perhaps even relationships where they have both messaged each other twice.

How can I modify my query to do this ?

Any help is much appreciated !

Thanks,
Mike123

CREATE TABLE [dbo].[tblMessage](
[MessageID] [int] IDENTITY(1,1) NOT NULL,
[MessageFrom] [int] NOT NULL,
[MessageTo] [int] NOT NULL,
[Message] [varchar](1500) NULL,
[prevMessage] [varchar](500) NULL,
[Subject] [varchar](50) NULL,
[date] [smalldatetime] NULL,
[Checked] [tinyint] NULL,
[deletedbySender] [tinyint] NULL,
[deletedbyRecipient] [tinyint] NULL,
[IP] [varchar](15) NULL,
[folderID] [int] NULL

)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-08 : 12:12:51

so you want messages in a particular converstion between two users? how do you distinguish mails of conversation? is it by subject?

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-10-08 : 12:30:40

Hi Visakh16,

I don't need to bring back the messages, or need to distinguish actual converstions.

For example:

If we pass messageFrom = 500

We want to bring back all messageTo's where the messageFrom has written 2 messages to MessageTo, as well as messageTo written 2 messages to MessageFrom.

Does that make senes ? We want to bring back a list of all the users the user has a conversational relationship with, and by that we mean each user has messaged each other twice.

Thanks!
Mike123

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-08 : 12:56:28

try like this.

SELECT m.messageFrom,m.messageTo
FROM tblMessage m
CROSS APPLY (SELECT COUNT(*) AS count
             FROM tblMessage
             WHERE messageTo=m.messageFrom
             AND messageFrom=m.messageTo) m1
WHERE m.messageFrom=500
AND m1.Count=2
GROUP BY m.messageFrom,m.messageTo
HAVING  COUNT(*)=2

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-10-08 : 13:14:48

seems to be working perfectly with test data so far, thank you very much!!! :)

cheers :)
mike123

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-08 : 13:19:02

quote:
Originally posted by mike123

seems to be working perfectly with test data so far, thank you very much!!! :)

cheers :)
mike123

welcome

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-08 : 13:25:35

How many records are there in tblMessage table?
What is the monthly growth?

Is there a time limit for within a message counts as a reply?

Which indexes exists on the table?

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources