[RESOLVED] Getting count=0

[Derokorian]

I'm havng a problem with a query. If I Inner join on the subquery, it doesn't return if there are no comments, if there is one or more it works fine. If i left join it works fine either way except that it doesn't show 0 for comment count. How do I get this to work so that it will return a 0 if there are no comments.

Thanks!

SELECT 
	p.`post_title` as `title`,
	p.`post_date` as `date`,
	p.`post_body` as `body`,
	u.`user_name` as `user`,
	c.`count` as `comments`
FROM `blog_posts` as p 
INNER JOIN
	`users` as u ON p.`post_user`=u.`id_user`
LEFT JOIN
	(SELECT COUNT(*) as `count`,`comment_post` FROM `blog_comments` GROUP BY `comment_post`) as c ON c.`comment_post`=p.`id_post`
ORDER BY p.`post_date` DESC LIMIT 1

[johanafm]

IFNULL(expr, value_when_null)

[Derokorian]

Thank you Johanafm!

SELECT 
	p.`post_title` as `title`,
	p.`post_date` as `date`,
	p.`post_body` as `body`,
	u.`user_name` as `user`,
	IFNULL(c.`count`,0) as `comments`
FROM `blog_posts` as p 
INNER JOIN
	`users` as u ON p.`post_user`=u.`id_user`
LEFT JOIN
	(SELECT COUNT(*) as `count`,`comment_post` FROM `blog_comments` GROUP BY `comment_post`) as c ON c.`comment_post`=p.`id_post`
ORDER BY p.`post_date` DESC LIMIT 1

Works perfectly! Much appreciated.