help with sql select and count

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 2005 Forums

Transact-SQL (2005)

help with sql select and count

Author

Topic

cwfontan
Yak Posting Veteran

87 Posts

Posted - 2009-04-13 : 12:25:54

I need help with a query to get data and a count of related records.. I have two related tables..

Blog_Messages AS m & Blog_Comments AS c

For each record in m I want to
SELECT *
FROM m
& SELECT Count From c WHERE m.messageID = c.messageID

display like this..

My Blog1, title, message, date
-Comments 3
My Blog2, title, message, date
-Comments 1

ETC..

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-13 : 13:36:33

Is this what you're looking for?


select m.[My Blog1]
       ,m.[title]
       ,m.[message]
       ,m.[date]
       ,count(*) as Comments
from   Blog_Messages AS m 
inner join Blog_Comments AS c
       on c.messageid = m.messageid
group by m.[My Blog1]
       ,m.[title]
       ,m.[message]
       ,m.[date]

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources