mysql quickie

ChicoMendez

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

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

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

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

ChicoMendez

seamus wrote:

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

4.1.9


TNX

seamus

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