Sql query driving me mad - 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 2005 Forums

Transact-SQL (2005)

Sql query driving me mad

Author

Topic

jonnyaltruistic
Starting Member

6 Posts

Posted - 2009-03-26 : 14:02:48

Hopefully I can explain this clearly, not complex just a little long.

I'm creating a 1-to-1 messaging system between users and 'experts' and I've got four tables - Message, Topic, UserTopic and Users. Usertopic is a link table containing all the userids and topicids of the topics they create.

The message table 5 columns:

MessageID,MessageText,TopicId,ExpertName,DateAdded

The usertopic table has 3 columns:

ID,Username,Topicid

I want a list of each username,expertname and topicId.

The broken query I have currently is:

SELECT ut.topicid, m.messageid, m.expertname,ut.username
FROM message m
Join usertopic ut on
m.topicid = ut.topicid

But this returns multiple entries if a topic has more than one message.

I just need a query that returns one entry for each topicid, irrespective of whether there's multiple messages in that topic. distinct fails...group by fails...
I'm tried and must be missing something obvious.
Any ideas anyone?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-26 : 14:13:35

which messageid and expertname would you want to see ?? Is there a criteria ?? If not, why have it in your select..

jonnyaltruistic
Starting Member

6 Posts

Posted - 2009-03-26 : 16:40:55

I'm going for a list of the messages and who the expert was who replied to it.

chrianth
Yak Posting Veteran

50 Posts

Posted - 2009-03-26 : 21:04:11

can you post a sample data for 2 topicID's with multiple replied message...

I initially think that you need to create a table that will have the distinct replied message from Message table by using max/min(DateAdded) and then join it to the main tables... It will be more helpful if you can post a sample data...

jonnyaltruistic
Starting Member

6 Posts

Posted - 2009-03-27 : 07:56:22

Here's an image of the tables with the data

When I run this query:

SELECT ut.topicid, m.messageid, m.expertname,ut.username
FROM message m
Join usertopic ut on
m.topicid = ut.topicid

I get the two messages in topicid 4.

So, imagine this is for an admin screen where I want a list of the different topics where the hasreply bit is false. Basically a list of the pending messages, ones that no expert has replied to.

I do have a seperate User table but it's not important to this problem. Any ideas anyone?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-27 : 08:12:29

Can you post the desired output basis your screenshot output and avoid the confusion ??

jonnyaltruistic
Starting Member

6 Posts

Posted - 2009-03-27 : 08:20:41

Something like this, note that there's no repetition despite there potentially being multiple messages in each topic, this listing only shows one entry per topic, and some details about it (last reply, num messages etc).

Hope this is clear.

I need the topicid returned so the 'click to view' link works.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-27 : 08:35:49

[# Message] is the count of messages ?

jonnyaltruistic
Starting Member

6 Posts

Posted - 2009-03-27 : 08:40:56

Sorry yes, should have mentioned that. It's a count of all messages in that topic.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-27 : 08:47:27

You perhaps need something like,

SELECT 
	username,
	[Last expert to reply]=(select top 1 expertname from message where topicid=s.topicid order by dateadded desc) ,
	topicid,
	[# Message]
FROM

	(
	SELECT 
		ut.username,
		--(select top 1 expertname from #message where topicid=m.topicid order by dateadded desc),
		ut.topicid, 
		[# Message]=count(m.messageid)
		
	FROM 
		message m
		Join usertopic ut on m.topicid = ut.topicid
	group by 
		ut.username,
		ut.topicid
	)s

I haven't put in the join for topic table. Add that in case you need topic details.

jonnyaltruistic
Starting Member

6 Posts

Posted - 2009-03-27 : 08:52:09

Wow...just tried it and it seems to work exactly as required. Thank you! I was getting extremely frustrated with it.

I'll have to go through your code to understand it. First thing I notice, which I didn't know I could do was to use the square brackets to denote the returned column name, very handy.

Thank you again, I'll put this into the system and see how it works, hopefully won't have to bother you with it again.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-27 : 08:55:13

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources