January 23, 2012 at 8:46 am
Hi. I have a simple stored proc that is meant to return for each row in the first table, a count of matching rows in the second table and a count of the rows in the third table that match those in the second table.
The result I am failing to get is:
ForumId Title Intro ThreadCount PostCount
1 Logging in Regarding logging in problems 1 2
2 Help Regarding help with this site 2 2
The SQL is
CREATE TABLE Forum
ForumId int -- Primary key
Title nvarchar()
Intro nvarchar()
CREATE TABLE Thread
ThreadId int -- Primary key
ForumId int -- Foreign Key to Forum table
Title nvarchar()
CREATE TABLE Post
PostId int -- Primary key
ThreadId int -- Foreign key to Thread table
Title nvarchar()
Message nvarchar()
INSERT INTO Forum
(Title, Intro)
VALUES ("Logging in", "Regarding logging in problems"),
VALUES("Help", "Regarding help with this site")
INSERT INTO Thread
(ForumId, Title)
VALUES (1, "Unable to log in to the site")
VALUES (2, "How do I create an account?")
VALUES (2, "Some buttons do not appear")
INSERT INTO Post
(ThreadId, Title, Message)
VALUES(1, "Unable to log in to the site", "I cannot log in to your site. Do yo know why?" )
VALUES(2, "How do I create an account", "I would like to create an account?" )
VALUES(2, "Re: How do I create an account", "Click on Register on any page" )
-- The SQL I was trying...
SELECT ForumId, Title, Intro,
(SELECT COUNT(ThreadId),
(SELECT COUNT(PostId)
FROM dbo.Post c WHERE c.ThreadId = a.ThreadId) AS PostCount
FROM dbo.Thread a WHERE a.ForumId = b.ForumId) AS ThreadCount
FROM dbo.Forum b
This code fails with a 'dbo.Thread.ThreadId is invalid in the select list' error
If I omit the Post query:
SELECT ForumId, Title, Intro,
(SELECT COUNT(ThreadId),
FROM dbo.Thread a WHERE a.ForumId = b.ForumId) AS ThreadCount
FROM dbo.Forum b
then the sql works.
Any help with this would be appreciated.
January 23, 2012 at 9:16 am
Hi and welcome to SSC. It was good that you included ddl and sample data. However, you clearly didn't test what you posted because the syntax was wrong all over the place.
For anybody else who want to play along I corrected the ddl and sample data (I also changed to temp tables but that is just my preference).
CREATE TABLE #Forum
(
ForumId int identity,
Title nvarchar(50),
Intro nvarchar(50)
)
CREATE TABLE #Thread
(
ThreadId int identity, -- Primary key
ForumId int, -- Foreign Key to Forum table
Title nvarchar(50)
)
CREATE TABLE #Post
(
PostId int identity, -- Primary key
ThreadId int , -- Foreign key to Thread table
Title nvarchar(50),
Message nvarchar(50)
)
INSERT INTO #Forum
(Title, Intro)
VALUES ('Logging in', 'Regarding logging in problems'),
('Help', 'Regarding help with this site')
INSERT INTO #Thread
(ForumId, Title)
VALUES (1, 'Unable to log in to the site')
, (2, 'How do I create an account?')
, (2, 'Some buttons do not appear')
INSERT INTO #Post
(ThreadId, Title, Message)
VALUES(1, 'Unable to log in to the site', 'I cannot log in to your site. Do yo know why?' ),
(2, 'How do I create an account', 'I would like to create an account?' ),
(2, 'Re: How do I create an account', 'Click on Register on any page' )
select * from #Forum
select * from #Thread
select * from #Post
I don't see any way possible your example query could possibly work but you did an excellent job explaining your desired output.
I think that something like this should work.
select *
from #Forum f
join (select COUNT(*) as ThreadCount, ThreadId, ForumId from #Thread group by ThreadId, ForumId) t on t.ForumId = f.ForumId
join (select COUNT(*) as PostCount, pcp.ThreadId from #Post pcp join #Thread pct on pct.ThreadId = pcp.ThreadId group by pcp.ThreadId) p on p.ThreadId = t.ThreadId
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 23, 2012 at 9:42 am
Thanks Sean for the prompt and informative reply. I think I will return to storing the Thread and Post count values in the Forum table and update them in the Insert, Update and Delete stored procedures.
January 23, 2012 at 12:10 pm
Try this query. It will work.
SELECT f.ForumId,f.Title,f.Intro,count(t.ThreadId) AS [Thread Count],count(p.PostId) AS [Post Count] FROM
Forum f inner join Thread t on f.ForumID = t.ForumID
inner join Post p on t.ThreadId = p.ThreadID
group by f.ForumId,f.Title,f.Intro
January 23, 2012 at 12:23 pm
See also 'SET QUOTED_IDENTIFIER (Transact-SQL)' in Books Online, the help system for SQL Server. Double quotes have a special meaning in SQL Server (by default).
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 23, 2012 at 12:33 pm
jeetendrajaiswal2000 (1/23/2012)
Try this query. It will work.SELECT f.ForumId,f.Title,f.Intro,count(t.ThreadId) AS [Thread Count],count(p.PostId) AS [Post Count] FROM
Forum f inner join Thread t on f.ForumID = t.ForumID
inner join Post p on t.ThreadId = p.ThreadID
group by f.ForumId,f.Title,f.Intro
But this approach is so much cleaner, easier to read and will perform way better than my version. 😉
Thanks for posting a query much better than my obvious brain fart came up with.
To the OP, this would be much preferred to the hack I put together so quickly.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 23, 2012 at 12:47 pm
a slightly different take on this.....
expanded the DDL that Sean provided
USE [tempdb]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Forum]') AND type in (N'U'))
DROP TABLE [dbo].[Forum]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Thread]') AND type in (N'U'))
DROP TABLE [dbo].[Thread]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Post]') AND type in (N'U'))
DROP TABLE [dbo].[Post]
GO
CREATE TABLE Forum
(
ForumId int identity,
Title nvarchar(50),
Intro nvarchar(50)
)
CREATE TABLE Thread
(
ThreadId int identity, -- Primary key
ForumId int, -- Foreign Key to Forum table
Title nvarchar(50)
)
CREATE TABLE Post
(
PostId int identity, -- Primary key
ThreadId int , -- Foreign key to Thread table
Title nvarchar(50),
Message nvarchar(50)
)
INSERT INTO Forum
(Title, Intro)
VALUES ('Logging in', 'Regarding logging in problems'),
('Help', 'Regarding help with this site'),
('Misc', 'Anything else')
INSERT INTO Thread
(ForumId, Title)
VALUES (1, 'Unable to log in to the site')
, (2, 'How do I create an account?')
, (2, 'Some buttons do not appear')
, (2, 'Why doesnt anyone answer')
, (1, 'Login fails with error')
INSERT INTO Post
(ThreadId, Title, Message)
VALUES(1, 'Unable to log in to the site', 'I cannot log in to your site. Do yo know why?' ),
(2, 'How do I create an account', 'I would like to create an account?' ),
(2, 'Re: How do I create an account', 'Click on Register on any page' ),
(4, 'Re: Why doesnt anyone answer', 'We have all gone to bed' ),
(4, 'Re: Why doesnt anyone answer', 'Someone will reply soon' ),
(4, 'Re: Why doesnt anyone answer', 'Your question is not clear' ),
(3, 'Re: Some buttons do not appear', 'Time for bed Zebedee' ),
(4, 'Re: Why doesnt anyone answer', 'try entering xyzzy' ),
(5, 'Re: Login fails with error', 'have you set up an id' ),
(5, 'Re: Login fails with error', 'id crewted but still no jpy' ),
(5, 'Re: Login fails with error', 'this is a PICNIC' )
SELECT Forum.ForumId, Forum.Title AS ForumTitle, Thread.ThreadId, Thread.Title AS ThreadTilte, Post.PostId, Post.Message
FROM Forum LEFT OUTER JOIN
Thread ON Forum.ForumId = Thread.ForumId LEFT OUTER JOIN
Post ON Thread.ThreadId = Post.ThreadId
--=== IS THE FOLLOWING CORRECT??
SELECT f.ForumId,f.Title,f.Intro,count(t.ThreadId) AS [Thread Count],count(p.PostId) AS [Post Count] FROM
Forum f inner join Thread t on f.ForumID = t.ForumID
inner join Post p on t.ThreadId = p.ThreadID
group by f.ForumId,f.Title,f.Intro
--== ANOTHER VIEW
SELECT Forum.ForumId ,
Forum.Title ,
COUNT( distinct Thread.ThreadId )AS ThreadCount ,
COUNT( distinct Post.PostId )AS PostCount
FROM
Forum LEFT OUTER JOIN Thread ON Forum.ForumId = Thread.ForumId
LEFT OUTER JOIN Post ON Thread.ThreadId = Post.ThreadId
GROUP BY Forum.ForumId , Forum.Title
ORDER BY Forum.ForumId
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply