INNER JOIN COUNT

[timoteius]

Hi Guys,

Need a little bit of help. I am trying to create a blog like feature that has the number of comments for that blog displayed underneath.
The database (access) has two tables, listed below..
-------
blog
-------
id
title
message
datewritten


---------------
comments_blog
---------------
id
comment
datewritten
username
blogid

Im having problems creating the sql for this to work. I can get the count and the blog to work seperately but not together.

Heres what I have thus far..

sqlquery = "SELECT * FROM blog INNER JOIN(SELECT COUNT(*) FROM comments_blog WHERE blogID = blog.id) ON blog.id = comments_blog.blogid"

Please help me..

 

[PHV]

SELECT *
FROM blog INNER JOIN (
SELECT blogId,COUNT(*) NbCom FROM comments_blog GROUP BY blogID
) N ON blog.id = N.blogid

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JarlH]

I'd do it like this:
[tt]
SELECT blog.*,
(SELECT COUNT(*) FROM comments_blog WHERE blogID = blog.id)
FROM blog
[/tt]

Core SQL-99/2003 etc.

 

[timoteius]

Thanks to both of you. The solutions work a treat.