CODEWITHSUNDEEP
mysql
Mostafa Elkady
Mostafa Elkady

Reputation: 5801

mysql query, select the top comment according votes

i have 2 tables comments | votes

the `votes` structure 
[`id` | `user_id` | `comment_id`  | `rating`]

and the comments has the comment_id as the primary ok? now i want get the top comments according to the sum of rating

[rating is 0 or 1]

and i want to get the top users too

Upvotes: 1

Views: 332

Answers (1)

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44363

Top Comments

This assumes comments table has a column named comments_id
SELECT A.* FROM comments A INNER JOIN (SELECT comment_id,SUM(rating) sumrating FROM votes GROUP BY comment_id) B USING (comment_id) ORDER BY B.sumrating;

This assumes comments table has a column named id
SELECT A.* FROM comments A INNER JOIN (SELECT comment_id,SUM(rating) sumrating FROM votes GROUP BY comment_id) B ON A.id = B.comment_id ORDER BY B.sumrating;

Top Users

This assumes users table has a column named user_id
SELECT A.* FROM users A INNER JOIN (SELECT user_id,SUM(rating) sumrating FROM votes GROUP BY user_id) B USING (user_id) ORDER BY B.sumrating;

This assumes users table has a column named id
SELECT A.* FROM users A INNER JOIN (SELECT user_id,SUM(rating) sumrating FROM votes GROUP BY user_id) B ON A.id = B.user_id ORDER BY B.sumrating;

Top Users and Comments

This assumes comments table has a column named comments_id and users has a column named user_id
SELECT B.* , C.* FROM (SELECT comment_id,user_id,SUM(rating) sumrating FROM votes GROUP BY comment_id,user_id) A comments B,users C, WHERE A.comment_id=B.comment_id AND A.user_id=C.user_id ORDER BY A.sumrating,C.user_id,B.comment_id;

Give it a Try !!!

Upvotes: 2

Related Questions