SQL Query for Forum Problem. - 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

General SQL Server Forums

New to SQL Server Programming

SQL Query for Forum Problem.

Author

Topic

eyeofthebeholder
Starting Member

4 Posts

Posted - 2010-01-21 : 10:21:00

Please view the query I am trying to produce in MSSQL.

Basically I am trying to create a basic forum which will integrate into an existing website with its own user-base, for a project I am doing. On most other forums, the forum list shows the last poster & date for the forum section, along with the total number of Topics and messages in the section. I would like to do this here in a single SQL Statement as I am putting it in a DataGrid in aspx.

The problem I am having is that when one of the sections has two different MesUserIDs it is showing up as different rows. I would like the 'General' row to show one row with:

General - Any comment o... - 2 - 3 - 12/01/2010 - 1350

Somehow the MesDTS and MesUserID fields need to be 'linked' to show one record. I have omitted the User table here, but it will be linked to the MesUserID to get the User's First and last name.

Anyone have any ideas on how to achieve this?

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-01-22 : 03:05:15

It's because you have a GROUP BY on the MesUserID. If you want Expr1 and Expr2 to be aggregated for MesUserID 1350 and 1741 you need to choose which of the two MesUserID's you want to display by assigning an aggregate function to that column also.

Basically what you need to do to fix the query is to change the MesUserID from Group By to a MAX or MIN.

- Lumbago
http://xkcd.com/327/

eyeofthebeholder
Starting Member

4 Posts

Posted - 2010-01-22 : 05:56:50

Hi, Thanks for the reply.

I this morning found another way round the query.

Basically I created a expression and combined the Date and the Users actual name from the User Table and set a Distinct Max on it. This seems to select the correct record.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-01-22 : 06:42:52

Would you mind posting your final query? From the sound of it there might be a possibility of some optimizations...a distinct max is not something I use too often.

- Lumbago
http://xkcd.com/327/

eyeofthebeholder
Starting Member

4 Posts

Posted - 2010-01-22 : 10:14:07

Hope is it OK posting in this way - cut and pasted from the code:

SELECT DISTINCT TblForumSection.SecID, TblForumSection.SecTitle, TblForumSection.SecDesc, COUNT(DISTINCT TblForumTopic.TopID) AS TopCount, COUNT(DISTINCT TblForumMes.MesID) AS MesCount, MAX(DISTINCT CONVERT(nvarchar, TblForumMes.MesDTS, 100) + N' by ' + TblUser.User_FirstName + N' ' + TblUser.User_Surname) AS LatestPost, TblForumSection.SecOrder

FROM TblForumTopic INNER JOIN TblForumSection ON TblForumTopic.TopSecID = TblForumSection.SecID INNER JOIN TblForumMes ON TblForumTopic.TopID = TblForumMes.MesTopID INNER JOIN TblUser ON TblForumMes.MesUserID = TblUser.User_ID

WHERE (TblForumSection.SecArchived = 0)

GROUP BY TblForumSection.SecTitle, TblForumSection.SecDesc, TblForumSection.SecID, TblForumSection.SecOrder

ORDER BY TblForumSection.SecOrder

Kristen
Test

22859 Posts

Posted - 2010-01-22 : 12:13:03

"Hope is it OK posting in this way"

I prefer it to the pretty picture

I think this will be more efficient (untested if the syntax has broken anything though)


SELECT	SecID, 
	SecTitle, 
	SecDesc, 
	CONVERT(nvarchar(99), TblForumMes.MesDTS, 100) 
		+ N' by ' 
		+ TblUser.User_FirstName 
		+ N' ' + TblUser.User_Surname) AS LatestPost, 
	TopCount, 
	MesCount, 
	SecOrder
FROM
(
	SELECT	TblForumSection.SecID, 
		TblForumSection.SecTitle, 
		TblForumSection.SecDesc, 
		COUNT(DISTINCT TblForumTopic.TopID) AS TopCount, 
		COUNT(DISTINCT TblForumMes.MesID) AS MesCount, 
		TblForumSection.SecOrder,
		MAX(TblForumMes.SomeMessageID) AS MesMaxID
	FROM TblForumTopic 
		INNER JOIN TblForumSection 
			ON TblForumTopic.TopSecID = TblForumSection.SecID 
		INNER JOIN TblForumMes 
			ON TblForumTopic.TopID = TblForumMes.MesTopID 
	WHERE (TblForumSection.SecArchived = 0)
	
	GROUP BY TblForumSection.SecTitle, TblForumSection.SecDesc, TblForumSection.SecID, TblForumSection.SecOrder
) AS X
	INNER JOIN TblForumMes 
		ON TblForumMes.SomeMessageID = MesMaxID
	INNER JOIN TblUser 
		ON TblForumMes.MesUserID = TblUser.User_ID
ORDER BY TblForumSection.SecOrder

That pair of COUNT(DISTINCT ...) in the inner loop bothers me though. If this query is hit a lot that will be a bottleneck. I would prefer to store the COUNTs in the database, and keep them updated with Trigger as posts are Created / Deleted, rather than calculating them every time someone looks at the Forum list.

Kristen
Test

22859 Posts

Posted - 2010-01-22 : 12:14:03

P.S. You should specify the width of your NVARCHAR - don't rely on the default width, you may find its not enough! (and your "intent" is not obvious to the next DEV looking at the code - which might be you!)

eyeofthebeholder
Starting Member

4 Posts

Posted - 2010-01-26 : 08:35:49

Thanks for the comment. On thinking about what you said, I have decided to create fields in the Topics and Sections tables for the Message and Topic counts. I have also added the same for the Latest Date/Time Stamp and UserID for each table. This has made the SQL more efficient and a lot less complicated!

As you say the pages that handle the creation/deletion of messages/topics will update these fields.

Thanks again

Kristen
Test

22859 Posts

Posted - 2010-01-26 : 08:43:26

"As you say the pages that handle the creation/deletion of messages/topics will update these fields."

Have a look at Triggers, if you are no familiar with them. That will enable you to ensure that the counts are correct, and that you don't have to code anything where you Insert or Delete rows (or Update relevant columns).

Otherwise you will have code in multiple places all trying to do the same thing, and something will get out-of-sync!

X002548
Not Just a Number

15586 Posts

Posted - 2010-01-26 : 10:34:46

quote:
Originally posted by eyeofthebeholder
I have decided to create fields in the Topics and Sections tables for the Message and Topic counts. I have also added the same for the Latest Date/Time Stamp and UserID for each table.

....and more of a pain to maintain...derived data is a stale as the microsecond after the insert/update

ALSO

You should get away from the GUI and go and use a query window....

Command line rocks

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Kristen
Test

22859 Posts

Posted - 2010-01-26 : 10:38:05

"....and more of a pain to maintain...derived data is a stale as the microsecond after the insert/update"

You are thinking 1NF, rather than real-world. Its a debate we can have over many pages of the thread, I'm sure!

Apart from the data being duplicative, if a Trigger maintains it then it will be accurate, surely?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources