SQL Question

AndyCr15 · 9 Jul 2018 at 17:43

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

Itchytrigg · 9 Jul 2018 at 19:13

I "think" changing your where to the following would do the trick to still return where there is no score;

Code:

where (month = 6 and year = 2018) or (month IS NULL)

Randomface74 · 9 Jul 2018 at 19:29

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

AndyCr15 · 9 Jul 2018 at 20:15

Itchy, I'm fairly sure I was missing some results? I'm not sure why.

Thanks Billy, that worked

Much appreciated!

dasyad · 12 Jul 2018 at 22:39

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.