SQL: Querying 2 tables and sort results by values in another table

Is it possible to select all the comments for a single record and order them by the overall ratio of positive:negative votes from another table?

I think I may need to use a subselect but I really am not sure how or where.

2 tables:

table 1 [Comments]

ID  |  RecordID  |  Comment  
------------------------------
1   | 100001     | blah blah
2   | 100202     | another co
3   | 100054     | lorem ips

table 2 [Ratings]

ID  | CommentID  |  Vote  
-------------------------
1   | 1          | 1       
2   | 1          | 0
3   | 1          | 1
4   | 3          | 0
5   | 3          | 0
6   | 3          | 0

Please note: 'Vote' : 0 = negative vote; 1 = positive vote Also, I am using Microsoft SQL Server

Thanks for any help :)

Answers


To sort them from positive to negative rating you can do:

SELECT c.id,
  c.recordID,
  c.comment
FROM comments c
INNER JOIN (
  SELECT CommentID,
  SUM(CASE WHEN Vote = 0 THEN -1 ELSE 1 END) AS RATING FROM ratings
  GROUP BY commentID
  ) r ON r.commentID = c.id
ORDER BY r.RATING DESC;

sqlfiddle demo

Note that i am using the CASE to give votes with 0 a value of -1, otherwise, 10 negatives and one positive would give a rating of 1.

This query is not taking into account the comments without scores. If you want to have the comments without scores to have 0 rating, you could do:

SELECT c.id,
  c.recordID,
  c.comment
FROM comments c
LEFT JOIN (
  SELECT CommentID,
  SUM(CASE WHEN Vote = 0 THEN -1 ELSE 1 END) AS RATING FROM ratings
  GROUP BY commentID
  ) r ON r.commentID = c.id
ORDER BY COALESCE(r.RATING,0) DESC;

EDIT:

To get the ratio try this:

SELECT c.id,
  c.recordID,
  c.comment,
  r.rating
FROM comments c
LEFT JOIN (
  SELECT CommentID,
    SUM(CASE 
        WHEN Vote = 1
          THEN 1
        ELSE 0
        END) / (nullif(COUNT(*) * 1.0, 0)) AS RATING
  FROM ratings
  GROUP BY commentID
  ) r ON r.commentID = c.id
ORDER BY COALESCE(r.RATING, 0) DESC;

sqlfiddle demo


Need Your Help

How to fork a large number of threads in OpenMP?

c multithreading openmp

for some reason I need to stress my processor and I want to fork a lot of threads in OpenMP. In pthreads you can easily do it using a for loop since it is forking a thread is just a function call. ...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.