SQL Query Question... - 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 2000 Forums

SQL Server Development (2000)

SQL Query Question...

Author

Topic

kwilliams

194 Posts

Posted - 2004-09-15 : 14:23:11

I have this SQL query in an ASP page that uses JScript syntax that needs to pull all posts from dbo.BulletinBoard (BB), and display them in order of their DateTime descending. I also need the count of replies to each BB post to be in the same statement, because I loop the results on the page. This is the query I wrote:

SELECT STATEMENT:

SELECT BB.Topic, BB.DateTime, ME.FName, ME.LName, BB.Cat_ID, ME.Email, BB.Post_ID, BB.Member_ID, ME.Member_ID AS Expr1, RE.Post_ID AS Expr2, (SELECT COUNT(*) FROM dbo.BulletinBoard AS BB WHERE BB.Post_ID = RE.Post_ID)AS OriginalPosts, (SELECT COUNT(*) FROM dbo.Replies AS RE WHERE BB.Post_ID = RE.Post_ID) AS Replies
FROM dbo.BulletinBoard BB INNER JOIN dbo.Members ME ON BB.Member_ID = ME.Member_ID LEFT JOIN dbo.Replies RE ON BB.Post_ID = RE.Post_ID
WHERE BB.Cat_ID = MMColParam
ORDER BY BB.DateTime DESC

VARIABLE:

Name Default Value Run-time Value
MMColParam BB.Cat_ID Session("Cat_ID")

My problem is that it displays all results from both dbo.BulletinBoard and dbo.Replies. I realize that this is because the statement includes:

LEFT JOIN dbo.Replies RE ON BB.Post_ID = RE.Post_ID

...but when I remove that join, I get an error stating that I can't use this Count(*) at the top of the statement:

SELECT COUNT(*) FROM dbo.Replies AS RE WHERE BB.Post_ID = RE.Post_ID) AS Replies

...without this join. Does anyone see a way around this issue? Thanks for any & all help.

KWilliams

kwilliams

194 Posts

Posted - 2004-09-15 : 14:34:23

I answered my own question shortly after adding this post. This is the revised SQL statement:

SELECT BB.Topic, BB.DateTime, ME.FName, ME.LName, BB.Cat_ID, ME.Email, BB.Post_ID, BB.Member_ID, ME.Member_ID AS Expr1, (SELECT COUNT(*) FROM dbo.Replies AS RE WHERE BB.Post_ID = RE.Post_ID) AS Replies
FROM dbo.BulletinBoard BB INNER JOIN dbo.Members ME ON BB.Member_ID = ME.Member_ID
WHERE BB.Cat_ID = MMColParam
ORDER BY BB.DateTime DESC

I removed the Count(*) for OriginalPosts, and removed the item RE.Post_ID, and it now works. Thanks anyway.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources