Reputation: 2180
I have three tables: Calendar, Employees, Task. Employees usually complete their tasks during the week and they don't work during the weekend. What I want to accomplish, is join the tables so I will see every day in a year even if no employee completed any task.
Here is an sql that works for me:
SELECT c.date, t.task, e.name
FROM calendar c
LEFT JOIN tasks t ON (c.date = t.date)
INNER JOIN employees e ON (t.emp_id = e.id)
WHERE c.date >= "2016-01-01" AND c.date <= "2016-01-07";
A result looks like this:
Date Task Name
...
2016-01-05 Driving John
2016-01-05 Cooking Rob
2016-01-06 Installing Jane
2016-01-07 null null
My problem is, that when I add an employees into WHERE
clause (WHERE e.name in("John", "Rob", "Jane")
), the last row (2016-01-07 null null
) disappears.
What should I change to keep even the dates without employees and tasks in the result? I need these dates to keep in for the final report.
Upvotes: 1
Views: 1431
Reputation: 8113
Remember to fix that date;
SELECT c.DATE
,t.task
,e.NAME
FROM calendar c
LEFT JOIN tasks t ON (c.DATE = t.DATE)
LEFT JOIN employees e ON t.emp_id = e.id
AND e.NAME IN ('John' ,'Rob','Jane')
WHERE c.DATE >= "2016-01-01"
AND c.DATE <= "2016-01-07";
Upvotes: 0
Reputation: 933
The other answer here is right. Turn the inner join into a left join to return days without an employee then
WHERE c.date between "2016-01-01" and "2016-01-07"
AND (e.name in("John", "Rob", "Jane") or e.name is null)
Upvotes: 1
Reputation: 20804
Change the inner join on employees to a left join, and put the name filter in the join clause, not the where clause.
Upvotes: 1