MySQL query with multiple INNER JOIN

Question

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!

Can you elaborate a bit on "this didn't work"?

Mureinik
– Mureinik

2014-10-03 13:19:37 +00:00
Commented Oct 3, 2014 at 13:19 — Mureinik
– Mureinik, Commented Oct 3, 2014 at 13:19

Gordon Linoff · Accepted Answer · 2014-10-03 16:44:37Z

1

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;

edited Oct 3, 2014 at 16:44

answered Oct 3, 2014 at 13:54

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

shaithana Over a year ago

Thanks, but this way I get back only the posts where there is at least one comment active. Posts with zero comments are ignored…

shaithana Over a year ago

My goal is to retrieve all the active posts and the number of comments per post, counting only the comments that are currently active.

Mike Deluca · Accepted Answer · 2014-10-03 13:22:47Z

0

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

Group By posts.*, authors.name

answered Oct 3, 2014 at 13:22

Mike Deluca

1,2202 gold badges20 silver badges45 bronze badges

Comments

Taras Velykyy · Accepted Answer · 2014-10-03 13:24:46Z

0

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

answered Oct 3, 2014 at 13:24

Taras Velykyy

1,8011 gold badge17 silver badges33 bronze badges

Comments

shaithana · Accepted Answer · 2014-10-03 15:41:19Z

0

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!

answered Oct 3, 2014 at 15:41

shaithana

2,4801 gold badge27 silver badges38 bronze badges

Collectives™ on Stack Overflow

MySQL query with multiple INNER JOIN

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related