MySQL blog and comments

programming
1

Hello all

My question is pretty simple but i don’t know the answer to this.

If i have a simple blog system that allows users to post comments to a blog.
How can i get a single blog and its attached comments from two different MySQl tables using one query. I know i have to use a join but when i do this i get the main blog content repeated each time for each comment.

can this be done or do i have to use two queries one to get the blog and the other its comments?

Also i have seen it the titles of some blogs the number of comments it has received how can this be done it a productive way?

Any help or advice would be greatly appericated.

oh thats what i have so far


SELECT * 
FROM cms_blog
LEFT JOIN cms_blog_comment ON cms_blog.blog_id = cms_blog_comment.blog_id
WHERE cms_blog.blog_id = 13
2

I’ve managed to do the blog comment count using this sql query



SELECT cms_blog.blog_id, blog_title, blog_teaser, count(cms_blog.blog_id) AS comment_count
FROM cms_blog LEFT JOIN cms_blog_comment ON cms_blog.blog_id = cms_blog_comment.blog_id
WHERE blog_enabled = 1
GROUP BY cms_blog.blog_id;