Count with Join - 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

General SQL Server Forums

New to SQL Server Programming

Count with Join

Author

Topic

hubare
Starting Member

11 Posts

Posted - 2006-05-02 : 10:20:51

Hello m'dears... As you may have gather I need help...

I have three tables (blog/comment/WM)
the blog lists the entries into the blog i'm creating whereas the comments table holds comments user have made to the blog entries... The WM table holds the usernames of the users... the schema looks like this

blog:
blogId
blogTitle
blogBody
WMid
blogNotes
blogCreated
blogModified

comments:
commentId
blogId
WMid
commentBody
commentDate

WM:
WMid
WMusername
WMpassword

I want to list all blog entries with a JOIN to the WM table to show the username but i also want to COUNT the amount of comments made for each entry...

I'm looking at it and it SEEMS really simple but i can't get my head around it and time's running out

any help or pointing out a topic i missed that covers this will be most appreciated

We're all going to hell... I guess I'll see you there!

Kristen
Test

22859 Posts

Posted - 2006-05-02 : 10:25:16

[code]
SELECT blogTitle, ...,
[NoComments] = (SELECT COUNT(*) FROM dbo.comments AS C WHERE C.blogId = B.blogId)
FROM dbo.blog AS B
JOIN dbo.WM AS WM
ON WM.WMid = B.WMid
ORDER BY B.blogCreate, B.blogId, ...
[/code]
Kristen

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-02 : 10:26:04

[code]
select b.blogId, w.WMusername, count(*)
from blog b inner join WM w
on b.WMid = w.WMid
inner join comments c
on b.blogId = c.blogId
group by b.blogId, w.WMusername
[/code]

hubare
Starting Member

11 Posts

Posted - 2006-05-02 : 10:29:34

Nice one.... thanx

We're all going to hell... I guess I'll see you there!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources