Twelvee Twelvee - 5 months ago 11
SQL Question

How to order sql query by other table values

i know that stackoverflow is remember answer for this question, but I have a bit different situation.
I have a lot cells in second table, but SQL query get only first. Ok, not all query, only WHERE tag.

Ex:

1 table:


user_id = 1
user_id = 2


2 table:


user_id = 1 | year = 2015 | rating = 55
user_id = 1 | year = 2016 | rating = 10
user_id = 2 | year = 2016 | rating = 50
user_id = 2 | year = 2016 | rating = 5


SQL query:


$query = "SELECT c.*,v.upvotes
FROM ".PREFIX."_users c
LEFT JOIN (SELECT user_id,pol,vid_sporta,year_sport,category,SUM(rating) as upvotes
FROM ".PREFIX."_userrating
GROUP BY user_id
) v
ON c.user_id = v.user_id
WHERE year_sport='2015'
ORDER BY upvotes DESC";


This query give me only one user, whitch has 2015 first.

I need some while cycle in SQL query :)
Help, please :)

Answer

It appears that there maybe a couple of issues going on.

First,

SELECT user_id,pol,vid_sporta,year_sport,category,SUM(rating) as upvotes

SHOULD BE

SELECT user_id,SUM(rating) as upvotes

You only want to include the columns that you are returning to the rest of your query and any column that is not apart of an aggregate function must also appear in your GROUP BY statement.

Second and this is why you may only be getting 1 record is the location of your WHERE year_sport = '2015'. Because the where condition is in the outer query it is treating your LEFT JOIN as an INNER JOIN and limiting to all results that have that year_sport in userrating. Move the where statement to your inner query and you should get all of your users but only upvotes from year_sport =2015.

$query = "SELECT c.*,v.upvotes
        FROM ".PREFIX."_users c
        LEFT JOIN (SELECT user_id,SUM(rating) as upvotes
        FROM ".PREFIX."_userrating
       WHERE year_sport='2015'
        GROUP BY user_id
        ) v
        ON c.user_id = v.user_id
        ORDER BY upvotes DESC";