to many results from joined table

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

SQL Server Development (2000)

to many results from joined table

Author

Topic

stsql
Starting Member

19 Posts

Posted - 2006-03-16 : 14:55:14

Hi all,

I've been working on a query that is working fine apart from I only want to retrieve one messageID (the latest MS.messageID)

However, it's currently returning results for all the messageID for that TP.topicID

I've attached the SQL below:


SELECT TP.topicTitle
     , TP.topicID
     , TP.topicSubTitle
     , TP.dateAdded
	 , TP.views
	 , TP.author
	 , TP.important
	 , TP.hottopic
	 , TP.answered
	 , TP.lastmemberdate
     , CG.categoryID
     , CG.categoryTitle
     , MB.nickname
	 , MB.memberID
	 , max(MS.messageID) as highest_messageID
     , count(MS.messageID) as numbReplies
  FROM (((
       f_categories as CG	
LEFT 
  JOIN f_topics as TP 
    ON CG.categoryID = TP.categoryID
       )
LEFT 
  JOIN f_messages as MS
    ON TP.topicID = MS.topicID
       )
LEFT 
  JOIN f_members as MB
    ON TP.memberID = MB.memberID
       )
 WHERE TP.active = '1'
   AND CG.categoryID = #URL.categoryID#
GROUP
    BY TP.topicTitle
     , TP.topicID
     , TP.topicSubTitle
     , TP.dateAdded
 	 , TP.lastmemberdate
	 , TP.views
 	 , TP.author
	 , TP.important
	 , TP.hottopic
	 , TP.answered
     , CG.categoryID
     , CG.categoryTitle
     , MB.nickname 
	 , MB.memberID
	 , MS.messageID
ORDER BY 	TP.important DESC, TP.dateAdded DESC

Any ideas what I'm doing wrong.
Thanks,

Richard

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-03-16 : 15:08:36

[code]SELECT TOP 1 TP.topicTitle
, TP.topicID[/code]

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

stsql
Starting Member

19 Posts

Posted - 2006-03-16 : 16:03:35

Hi Lumbago,

Thanks for the response.

That's not quite what I was looking for. I need all TP.toicTitle returned where TP.categoryID is the current CG.categoryID. I then want to return only the latest MS.messageID for the TP.topicID currentrow.

I tried using that function on "TOP 1 MS.messageID" but that only returned the one result in total.

I think this might be a clearer way for me to describe it:
I need to get details of the current category >> returing all the topics that sit under that category >> plus the originator (in f_members ) of each topic & the latest f_message for that each topic

Thanks,

Richard

jhermiz

3564 Posts

Posted - 2006-03-16 : 18:27:15

Or here's a better idea ;), post at least 5-10 sample rows of data between the tables and show us the expected output you would want.

How does that sound ?

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

stsql
Starting Member

19 Posts

Posted - 2006-03-17 : 12:38:39

Hi jhermiz,

I'm not sure of the best way to write that info out so I have added the table fields and example data to a single xls page:
http://66.165.75.51/example.xls

My example would be that:
1) user is in the category where categoryID=1
2) the topics that this would grab would be topicID=1,2,4,5
3) I'd loop over each row for these
#important#
#hottopic#
#answered#
#dateAdded#
#topicid#
#topicTitle#
#topicSubTitle#
#numbReplies#
#views#
#author#
#messageID#
#lastmemberdate#
#nickname#
4) which would return this recordset:
http://66.165.75.51/results.xls

What do think I need to do to my sql statement?
Thanks,

Richard

stsql
Starting Member

19 Posts

Posted - 2006-03-23 : 04:31:20

Bump

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources