CODEWITHSUNDEEP
mysqlsql
StefanK
StefanK

Reputation: 2180

How to join tables to see dates with null values?

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

Answers (3)

Rich Benner
Rich Benner

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

Ambulare
Ambulare

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

Dan Bracuk
Dan Bracuk

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

Related Questions