Reputation: 525
I have a two tables in a database: employees and employee vacation. The employees table has only one column for an employee name and the employee vacation has two columns, one containing the employee name and one containing the day they took vacation. I have a query that returns (correctly) the number of times the employee name appears in the vacation table but I am trying to figure out a way to return an entry of employee name | 0 for cases where the employee name exists in the employee table but not in the employee_vacation table. Is there a way I can modify this query to do this? Query is below
select e.EMPLOYEE_NAME, COUNT(v.VACATION_DAY) AS VACATION_DAYS
from EMPLOYEE_VACATION as v
LEFT JOIN EMPLOYEES AS e USING(EMPLOYEE_NAME)
GROUP BY e.EMPLOYEE_NAME
Upvotes: 0
Views: 251
Reputation: 17177
You are doing it almost correctly. To return an employee even if there are no rows in employee_vacation table, you actually need to switch your joining so that you start with a table EMPLOYEES
containing all the employees and LEFT JOIN
the information about their vacation:
SELECT e.EMPLOYEE_NAME, COUNT(v.VACATION_DAY) AS VACATION_DAYS
FROM EMPLOYEES e
LEFT JOIN EMPLOYEE_VACATION v USING (EMPLOYEE_NAME)
GROUP BY e.EMPLOYEE_NAME
Or simply use a RIGHT JOIN
instead (to limit the code changes if you're lazy):
SELECT e.EMPLOYEE_NAME, COUNT(v.VACATION_DAY) AS VACATION_DAYS
FROM EMPLOYEE_VACATION v
RIGHT JOIN EMPLOYEES e USING (EMPLOYEE_NAME)
GROUP BY e.EMPLOYEE_NAME
I prefer using LEFT
as opposed to RIGHT
joins because I'm reading the query from top to bottom and this seems more logical to me not to go backwards when trying to understand the logic.
Upvotes: 1