PHP MySQL Left Join also using WHERE x IN y

I have been trying to get the following SQL to work:

SELECT user.id,
       user.firstname,
       user.surname,
       user.gender,
       user.username,
       user.school_name,
       user.grade,
       user.vehicle,
       user.price,
       COUNT(student_reviews.userid) as reviews
 FROM user
 LEFT JOIN student_reviews ON student_reviews.userid = user.id
 WHERE user.id IN ($results)
 GROUP BY (student_reviews.userid) 

The $results variable I'm passing in is '2,13,1'.

I only ever seem to get the first row back. It was working until I changed it to a left join to accommodate users that don't have any reviews.

I'm sure it's something simple, I'm just stumped.

Thanks

Update

It appears that when adding COUNT() or GROUP BY to the开发者_StackOverflow中文版 query, it only returns one result. It works until the COUNT() function is added

The problem here is that you are grouping by the column which can be null: student_reviews.userid (it's evaluated to null by left join for users without reviews). You should be grouping by user.id, and then you should get count zero for users without reviews. I'm guessing that the user you are getting as a single result has at least 1 review, while others have none.

Why not try a subquery to get the number of reviews?

SELECT user.id,
       user.firstname,
       user.surname,
       user.gender,
       user.username,
       user.school_name,
       user.grade,
       user.vehicle,
       user.price,
       (
          SELECT COUNT(*) FROM student_reviews sr WHERE sr.userid = user.id
       ) as reviews
  FROM user
  WHERE user.id IN ($results)

When solving similar problems I go this route, because left join + group by usually doesn't give me the kind of results I want.