SQL Question

Soldato
Joined
28 Apr 2011
Posts
14,762
Location
Barnet, London
Hi all, I wonder if someone can help with this -

Code:
SELECT
  firstname,
  surname,
  employee.is,
  COUNT(score)
FROM partners
LEFT JOIN scores
  ON employee.id= score.employee_id
WHERE
MONTH = 6
AND YEAR = 2018
GROUP BY score.employee_id
ORDER BY COUNT(score) ASC;

I have an employees table and a scores view, joined by the id & employee_id keys.

When an employee achieves something, they are awarded points (into the scores view). The above code returns how many scores an employee has received in that month, but if they have none, nothing is returned.

I know a LEFT JOIN should return everything in the employee table, but I also realise my WHERE terms mean it will ignore those that haven't received a score that particular month.

How can I get a complete list of the employees returned, with 0 if they haven't scored anything?

Thanks :)
 
Permabanned
Joined
9 Aug 2009
Posts
12,236
Location
UK
Code:
SELECT e.Id, e.FirstName, e.Surname,
(
   SELECT COUNT(*)
   FROM Scores s
   WHERE s.Employee_Id = e.Id
   AND s.Year = 2018
   AND s.Month = 6
) As ScoreCount
FROM Employees e
ORDER BY ScoreCount DESC
 
Associate
Joined
15 Mar 2008
Posts
1,880
Code:
SELECT
  firstname,
  surname,
  employee.is,
  COUNT(score)
FROM partners
LEFT JOIN scores
  ON employee.id= score.employee_id
AND MONTH = 6
AND YEAR = 18
GROUP BY score.employee_id
ORDER BY COUNT(score) ASC;

I’d prefer moving the condition to the join, that way you avoid an unnessecary subquery. Be interesting to see the query plan of the two, they might end up being identical.

:)
 
Back
Top Bottom