[SOLVED] help with a query

[garry]

So I'm making a pretty simple blog using the codeigniter framework. Basically, in my query to get the latest 10 posts for the front page, I wanted to include the number of comments each post has.

 

My tables are as follows:

 

blog_posts

blog_comments

 

blog_comments has a field called "post_id" with the id of the corresponding post. Is there a way I can include the number of comments each post has? I'd love to get it all in one query to help with load times and such. Here's the current query I'm using to get all the post info..

 

SELECT blog_posts.id, blog_posts.user_id, blog_posts.title, blog_posts.body, blog_posts.created, blog_posts.modified, users.username AS author
FROM (blog_posts)
LEFT JOIN users ON users.id = blog_posts.user_id
ORDER BY created DESC
LIMIT 10

[garry]

Come on, nobody? Surely someone has an idea?

[Mikedean]

You can run a SELECT query inside a query so long as it returns only 1 value.

 

E.g.

 

SELECT (SELECT * FROM table LIMIT 0, 1) as Result, column1, column2 FROM table etc.

[garry]

You can run a SELECT query inside a query so long as it returns only 1 value.

 

E.g.

 

SELECT (SELECT * FROM table LIMIT 0, 1) as Result, column1, column2 FROM table etc.

You're a genius Thank you!

[garry]

Ah, I spoke too soon! This sort of does what I want, but how do I tell it to return the number of comments for each individual row returned? At the moment, I can only tell it to return the total amount of comments. Is there a mysql command that lets you do it for a particular row? Here's an example of my query:

 

SELECT `blog_posts`.`id`, `blog_posts`.`user_id`, `blog_posts`.`title`, `blog_posts`.`body`, `blog_posts`.`created`, `blog_posts`.`modified`, `users`.`username` AS author,
(SELECT COUNT(*) FROM blog_comments  WHERE ?what do i put here?) AS num_coms
FROM (`blog_posts`)
LEFT JOIN `users` ON `users`.`id` = `blog_posts`.`user_id`
ORDER BY `created` DESC
LIMIT 10

[Mikedean]

This is a stab in the dark, but try the following.

 

(SELECT COUNT(blog_comments.post_id) FROM blog_comments, blog_posts WHERE blog_comments.post_id = blog_posts.id) AS num_coms

[garry]

This is a stab in the dark, but try the following.

 

(SELECT COUNT(blog_comments.post_id) FROM blog_comments, blog_posts WHERE blog_comments.post_id = blog_posts.id) AS num_coms

woot, that was pretty much perfect, just had to remove the blog_posts table from the FROM clause and it works perfectly

 

(SELECT COUNT(id) FROM blog_comments WHERE blog_comments.post_id = blog_posts.id) AS num_coms