CODEWITHSUNDEEP
sql
user3961151
user3961151

Reputation:

How to order by other table?

I'm working on Stackoverflow-like system for my school project on ASPX. I'm already way beyond what the teacher thought us, so I decided to use the stupid way of counting down and upvotes - Having a spacial table for each vote.

Here are both of my tables:

comments
    id | content | by    | bestanswer
    1  | demo    | Vlad  | 0
    2  | sample  | Hagay | 1
    3  | chacking| Gil   | 0
    4  | trying  | Teddy | 0
    ...| ...     | ...   | ...
votes
    postid | username | upvote
    1      | Gil      | 1
    1      | Hagay    | 1
    1      | Teddy    | -1
    2      | Gil      | -1
    3      | Vlad     | -1
    2      | Gil      | 1
    ...    | ...      | ...

How can I, using SQL, SELECT all and set the one with bestanswer=1 as the first, and then order by the ammount of upvotes (2, 1, 3)?

Upvotes: 0

Views: 52

Answers (1)

Hart CO
Hart CO

Reputation: 34774

You can aggregate the "upvotes" in a subquery, then JOIN to that table for ordering:

SELECT c.*,v.upvotes
FROM comments c
LEFT JOIN (SELECT postid,SUM(upvote) as upvotes
           FROM votes
           GROUP BY postid
           ) v
  ON c.id = v.postid
ORDER BY c.bestanswer DESC, upvotes DESC

I'm assuming that id and postid relate to each other, and that you want to return all fields from the comments table, and maybe the total votes as well.

Upvotes: 2

Related Questions