Subquery with multiple rows - 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 2008 Forums

Transact-SQL (2008)

Subquery with multiple rows

Author

Topic

filyr
Starting Member

4 Posts

Posted - 2010-10-31 : 10:25:43

Hello,

This is a pretty easy question, but I've got some brainlags atm and can't figure it out:

This query should return the comment-count from all the subforums from a specific category, but it obviously only gives me the count from the first subcategory, since it only will use the first row from this subquery, even though there might be more rows returned.

SELECT id FROM subcategories WHERE belongs_to_category=1

Full query:


select COUNT(*) from comments where belongs_to_topic in ( select id from topics where belongs_to_categor in ( SELECT id FROM subcategories WHERE belongs_to_category=1 ) and in_subcategories='yes')

I want it to get all the rows, but I can't really figure it at the moment. Make a join maybe? Thanks

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-10-31 : 12:47:02

A clue as to the structures would be nice but maybe something like

select sc.id, count(*)
from subcategories sc
join topics t
on t.belongs_to_categor = ec.id
join comments c
on c.belongs_to_topic = t.id
group by sc.id

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources