MYSQL Join help. Get results based on number of comments in seperate comments table?

Question

Beginner here! I am trying to write a query that will select the 3 most commented on results from a "results" table the comments are stored in a seperate "comments" table.

results
 - id  
 - title    
 - body   
 - etc
 - etc

comments
 - id  
 - result_id
 - user_id
 - timestamp
 - comment

So I need to select all from results and order by the amount of matches between results.id and comments.result_id but I don't really know where to start!

Thanks a lot for the help, it's much appreciated!

Kayser · Accepted Answer · 2012-07-24T15:44:37.547

2

Not tested but you can do something like that

SELECT  r.id  ,r.title, r.body
FROM results r INNER JOIN (SELECT result_id, count(id) cnt FROM comments GROUP BY result_id) c
ON r.id = c.result_id
ORDER by c.cnt DESC

edited Jul 24 '12 at 15:44

answered Jul 24 '12 at 15:33

Kayser

6,544
19
53
86

Please use proper join syntax when posting answers. (Replace the "," in the from with "join" and change the "where" to "on".) – Gordon Linoff Jul 24 '12 at 15:39
@GordonLinoff Thanks for the hint. It was too fast to think about it :-) – Kayser Jul 24 '12 at 15:45

score 1 · Answer 2 · answered Jul 24 '12 at 15:31

1

Perhaps try something like this:

SELECT COUNT(c.id) AS comment_count FROM results r
LEFT JOIN comments c ON r.id=c.result_id
GROUP BY result_id ORDER BY comment_count DESC LIMIT 3;

answered Jul 24 '12 at 15:31

James

11
1

You probably want to include the results_id in the SELECT clause. – Gordon Linoff Jul 24 '12 at 15:33

score 1 · Answer 3 · edited May 23 '17 at 10:09

The following should work:

SELECT r.id, COUNT(r.id) AS comment_count 
FROM results r 
INNER JOIN comments c 
ON results.id = c.result_id 
GROUP BY r.id 
ORDER BY comment_count DESC

You join the two tables where the id of the result is the same as the referenced result_id from the comments table. Then you group the rows by result_id to remove duplicates. The COUNT() function sums up the grouped rows and displays the number of them.

Then just sort the result based on the generated comment count.

You could use LEFT OUTER JOIN as well, then you would get all results that have no comments as well. If you want this or not depends on your needs.

For a description of SQL joins, check out What is the difference between "INNER JOIN" and "OUTER JOIN"?

MYSQL Join help. Get results based on number of comments in seperate comments table?

3 Answers3