CODEWITHSUNDEEP
mysql
Antony Sastre
Antony Sastre

Reputation: 617

How do I return rows based on associated table row count in MySQL?

If I have a posts table and a comments table associated to it. Can I fetch a list of Post depending on the comment count in a single query? I.e. I only want a list of posts with 2 or more comments.

I'm aware this doesn't work but it illustrates what I wish to do.

SELECT * FROM posts p LEFT JOIN comments c ON c.post_id == p.id WHERE COUNT(c) > 2

POSTS
| id | title                 |
------------------------------
| 1  | Post with 1 comment   |
| 2  | Post with 3 comments  |
| 3  | Post with 4 comments  |
| 4  | Post with 0 comments  |

COMMENTS
| id | comment    | post_id  |
------------------------------
| 1  | Hello ...  | 1        |
| 2  | Good ...   | 2        |
| 3  | Hi ...     | 2        |
| 4  | Yes ...    | 2        |
| 5  | Good ...   | 3        |
| 6  | Hi ...     | 3        |
| 7  | Yes ...    | 3        |
… and so forth …

Upvotes: 0

Views: 37

Answers (2)

AdamMc331
AdamMc331

Reputation: 16691

The reason your query doesn't work is because the HAVING clause is used for conditions regarding aggregation. You cannot use the WHERE clause for this. To quote the linked page:

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

Try the following query:

SELECT p.*
FROM posts p
LEFT JOIN comments c ON c.post_id = p.id
GROUP BY p.id
HAVING COUNT(*) > 2;

Note also that because you are preforming aggregation, you will need to GROUP BY the post_id so you will see one resulting row for each post.

I would also like to point out that by grouping like this and only receiving one row for each post, you will be unable to properly display the comment information. If you would like to see the comments for all posts that have more than two comments, you will need to join your query above with the comment table:

SELECT c.*
FROM comments c
JOIN(
  SELECT p.id
  FROM posts p
  LEFT JOIN comments c ON c.post_id = p.id
  GROUP BY p.id
  HAVING COUNT(*) > 2) p ON p.id = c.post_id;

Here is an SQL Fiddle example.

EDIT

If you want to search for posts that have more than two comments where one comes from a specific user, you can adjust the inner query with aggregation. You can add an extra condition to your HAVING clause to check that the SUM(user_id = 1) is greater than 0. Try this:

SELECT p.*
FROM posts p
LEFT JOIN comments c ON c.post_id = p.id
GROUP BY p.id
HAVING COUNT(*) > 2 AND SUM(c.user_id = 1) > 0;

Here is an updated SQL fiddle. I adjusted your sample data where user_id 1 only commented on a certain post, so you can see that it is indeed only that post that is returned.

Upvotes: 2

Alex
Alex

Reputation: 17289

You can't put condition with aggregating function like SUM, MIN, MAX, COUNT etc in WHERE clause. You should put such type of condition in HAVING clause after you set your GROUP BY statement.

That means WHERE executes for every record fetched from database (and for one record MIN, MAX, COUNT etc has no sense) and HAVING executes for every group row you asked for.

SELECT p.*, COUNT(*) 
FROM posts p 
LEFT JOIN comments c 
ON c.post_id = p.id 
GROUP BY p.id
HAVING COUNT(c.id) > 2

Upvotes: 1

Related Questions