SQL Server Performance Forum – Threads Archive

Joined query problem

I am building a small forum section in my website. I have two tables, Forum and Forum_Child Forum table definition:
CREATE TABLE [dbo].[Forum](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Title] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Description] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Post] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Submitter] [int] NOT NULL,
[Views] [int] NULL,
[DateSubmitted] [datetime] NOT NULL,
[LastUpdated] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] Forum Child:
CREATE TABLE [dbo].[Forum_Child](
[ID] [int] IDENTITY(1,1) NOT NULL,
[PID] [int] NOT NULL,
[Post] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DateSubmitted] [datetime] NOT NULL,
[Submitter] [int] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] I am writing a query to pull back all the parent topics along with the info on the most recent post. Last Updated info will be most recent reply if one exists, or original post info if nobody has replied. The query is doing everything I want it to do, but I can’t figure out the best way to get the most recent submitter. I can’t use max like I am doing on the date Submitted. Here is the query:
SELECT ForumID = F.ID
, F.Title
, [Description] = F.[Description]
, Replies = ISNULL((SELECT COUNT(*) FROM Forum_Child WHERE PID = F.ID), 0)
, Submitter = P.FirstName + ‘ ‘ + P.LastName
, ‘Last Action’ = ISNULL(MAX(FC.DateSubmitted), F.DateSubmitted)
FROM Forum F
INNER JOIN Player P ON F.Submitter = P.ID
LEFT OUTER JOIN Forum_Child FC ON FC.PID = F.ID
GROUP BY F.ID
, F.Title
, F.[Description]
, F.Replies
, P.LastName
, P.FirstName
, F.LastUpdated
, F.DateSubmitted
ORDER BY [Last Action] DESC
Any Ideas?? Any and all optimization suggestions would be appreciated. I realize I could do this all in one table, but it seems a little more straight forward to use a child table. Is this a bad idea?

How about somthing like this ???
SELECT ForumID = F.ID, F.Title, [Description] = F.[Description],
FC.Replies , FC.[Last Action]
FROM Forum F
INNER JOIN Player P ON F.Submitter = P.ID
LEFT OUTER JOIN
(
Select Count(1) As Replies, [PID],
ISNULL(MAX(FC.DateSubmitted), F.DateSubmitted) [Last Action] Forum_Child
Group by [PID]
) As FC On FC.[PID] = F.[ID]
ORDER BY [Last Action] DESC
Chirag

Not directly an answer, but I would cheat here and have a look at some of the available opensource forum packages how they do this. Actually I would use rather one of these packages and modify it, then reinvent the wheel and write my own forum package. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

Thanks for the input, the problem was that I also needed the Submitter from the child table and there was know way to use max to get it. Here is what I came up with throught trial and error and looking at some other posts. It seems to work: SELECT F.ID
, F.Title
, [Description] = F.[Description]
, Replies = ISNULL((SELECT COUNT(*) FROM Forum_Child WHERE PID = F.ID), 0)
, Views = ISNULL(Views, 0)
, Submitter = CASE WHEN ChildSub.Submitter IS NOT NULL THEN ChildSub.Submitter ELSE P.FirstName + ‘ ‘ + P.LastName END
, LastUpdated = CASE WHEN FFC.DateSubmitted IS NOT NULL THEN FFC.DateSubmitted ELSE F.DateSubmitted END
FROM Forum F
INNER JOIN Player P ON F.Submitter = P.ID
LEFT OUTER JOIN
(
SELECT PID, MAX(DateSubmitted) AS DateSubmitted, MAX(ID) AS ID
FROM Forum_Child
GROUP BY PID
) AS FFC ON F.ID = FFC.PID
LEFT OUTER JOIN
(
SELECT MAX(FC.ID) AS ID, Submitter = P.FirstName + ‘ ‘ + P.LastName
FROM Forum_Child FC
INNER JOIN Player P ON P.ID = FC.Submitter
Group by P.FirstName, P.LastName
) AS ChildSub ON FFC.ID = ChildSub.ID
ORDER BY LastUpdated DESC
I tried using a single join with a top 1 select query, but that doesn’t work because the query is being used as a join condition, and I end up matching that top 1 record for all records in the Forum Table. I think this works correctly. Is it optimized?

Can you post some sample data with their expected result?? Chirag

]]>