1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

SQL Question

Discussion in 'HTML, Graphics & Programming' started by AndyCr15, Jul 9, 2018.

  1. AndyCr15

    Soldato

    Joined: Apr 28, 2011

    Posts: 7,211

    Location: London, UK

    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 :)
     
  2. Itchytrigg

    Wise Guy

    Joined: Jan 27, 2009

    Posts: 1,141

    Location: Oxfordshire

    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)
     
  3. billysielu

    Sgarrista

    Joined: Aug 9, 2009

    Posts: 9,468

    Location: Oxfordshire

    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
    
     
  4. AndyCr15

    Soldato

    Joined: Apr 28, 2011

    Posts: 7,211

    Location: London, UK

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

    Thanks Billy, that worked :) Much appreciated!
     
  5. dasyad

    Wise Guy

    Joined: Mar 15, 2008

    Posts: 1,808

    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.

    :)