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.
| Author |
Topic |
|
jimsurf
Starting Member
18 Posts |
 Posted - 2006-09-17 : 17:29:22
|
| I am building a small forum section in my website. I have two tables, Forum and Forum_ChildForum 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.IDGROUP BY F.ID , F.Title , F.[Description] , F.Replies , P.LastName , P.FirstName , F.LastUpdated , F.DateSubmitted ORDER BY [Last Action] DESCAny 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? |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-09-17 : 18:28:36
|
| LEFT OUTER JOIN Forum_Child FC ON FC.PID = F.IDand FC.PID = (select max(FC2.PID) from Forum_Child FC2 where FC2.PID = F.ID)And I don't think you need the group by with this.You might onsider maintaining the count and last post on the F table or another new one.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
jimsurf
Starting Member
18 Posts |
Posted - 2006-09-18 : 22:35:54
|
| 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 ENDFROM Forum F INNER JOIN Player P ON F.Submitter = P.ID LEFT OUTER JOIN (SELECT PID, MAX(DateSubmitted) AS DateSubmitted, MAX(ID) AS IDFROM Forum_ChildGROUP BY PID) AS FFC ON F.ID = FFC.PID LEFT OUTER JOIN (SELECT MAX(FC.ID) AS ID, Submitter = P.FirstName + ' ' + P.LastNameFROM Forum_Child FCINNER JOIN Player P ON P.ID = FC.SubmitterGroup by P.FirstName, P.LastName) AS ChildSub ON FFC.ID = ChildSub.IDORDER BY LastUpdated DESCI 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? |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
|
|
|
|
|
|
|
Joined query problem