mysql quickie

ChicoMendez · 30-09-2006 11:08AM #1

Hi

i have 2 tables

blog and comments,

how do i get a listing of the blogs with a count of the comments on each blog using one mysql statement?
(ive tryed a few to no avail)

Tnx

seamus · 30-09-2006 11:15AM

Try.....

"SELECT blog.*, count(comments.id) from BLOG
LEFT JOIN comments on comments.blogid = blog.id
GROUP BY comments.blogid"

Shot in the dark, but I think it's in the direction you want to go.

ChicoMendez · 30-09-2006 03:37PM

seamus wrote:

Try.....

"SELECT blog.*, count(comments.id) from BLOG
LEFT JOIN comments on comments.blogid = blog.id
GROUP BY comments.blogid"

Shot in the dark, but I think it's in the direction you want to go.

Thanks.. now just to conplicate things..
the comment table is used for BLOGS and IMAGES and OTHER therefore i need to add a clause like WHERE comment.type=2 (2 is for blogs!)

however that will only return the blogs with comments....
any ideas?

TNX

seamus · 01-10-2006 11:04PM

It may be possible to do using nested queries. What version of MySQL are you running?

ChicoMendez · 02-10-2006 10:36AM

seamus wrote:

It may be possible to do using nested queries. What version of MySQL are you running?

4.1.9

TNX

seamus · 02-10-2006 10:56AM

OK, this may need a little tweaking to get right/debugged

SELECT blog.*, blogdetails.blogcount
FROM blog
LEFT JOIN (SELECT sqc.blogid, count(sqc.id) as blogcount
FROM comment sqc
WHERE sqc.type = 2
GROUP BY sqc.id) blogdetails ON blogdetails.blogid = blog.id

mysql quickie

Comments