CODEWITHSUNDEEP
mysqljoin
thurmc
thurmc

Reputation: 525

MySQL join include data also when it doesn't exist in another table

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

Answers (1)

Kamil Gosciminski
Kamil Gosciminski

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

Related Questions