CODEWITHSUNDEEP
mysqlsql
shaithana
shaithana

Reputation: 2490

MySQL query with multiple INNER JOIN

I'm a little bit confused about a stupid query:

I get rows from the table posts joined with the table authors and the table comments, in a way like this:

SELECT posts.*, authors.name, COUNT(comments.id_post) AS num_comments
FROM posts JOIN authors ON posts.id_author = authors.id_author
LEFT JOIN comments ON posts.id_post = comments.id_post
WHERE posts.active = 1
AND comments.active = 1

this doesn't work, of course.

What I try to do is to retrieve:

1) all my active post (those that were not marked as deleted);

2) the names of their authors;

3) the number of active comments (those that were not marked as deleted) for each post (if there is at least one);

What's the way? I know it's a trivial one, but by now my brain is in offside… Thanks!

Upvotes: 0

Views: 63

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Presumably, id_post uniquely identifies each row in posts. Try this:

SELECT p.*, a.name, COUNT(c.id_post) AS num_comments
FROM posts p JOIN
     authors a
     ON p.id_author = a.id_author LEFT JOIN
     comments c
     ON p.id_post = c.id_post
WHERE p.active = 1 AND c.active = 1
GROUP BY p.id_post;

Note that this uses a MySQL extension. In most other databases, you would need to list all the columns in posts plus a.name in the group by clause.

EDIT:

The above is based on your query. If you want all active posts with a count of active comments, just do:

SELECT p.*, a.name, SUM(c.active = 1) AS num_comments
FROM posts p LEFT JOIN
     authors a
     ON p.id_author = a.id_author LEFT JOIN
     comments c
     ON p.id_post = c.id_post
WHERE p.active = 1 
GROUP BY p.id_post;

Upvotes: 1

shaithana
shaithana

Reputation: 2490

I found the correct solution: 

SELECT posts.id_post, authors.name, COUNT(comments.id_post) AS num_comments
FROM posts JOIN authors
ON posts.id_author = authors.id_author
LEFT OUTER JOIN comments
ON (posts.id_post = comments.id_post AND comments.active = 1) 
WHERE posts.active = 1
GROUP BY posts.id_post;

Thanks everyone for the help!

Upvotes: 0

Taras Velykyy
Taras Velykyy

Reputation: 1801

You should you GROUP BY clause together with aggregate functions. Try something similar to:

SELECT posts.*, authors.name, COUNT(comments.id_post) AS num_comments
FROM posts JOIN authors ON posts.id_author = authors.id_author
LEFT JOIN comments ON posts.id_post = comments.id_post
-- group by
GROUP BY posts.*, authors.name
--
WHERE posts.active = 1
AND comments.active = 1

Upvotes: 0

Mike Deluca
Mike Deluca

Reputation: 1210

Since you are doing a count, you need to have a group by. So you will need to add

Group By posts.*, authors.name

Upvotes: 0

Related Questions