Select rows from specific date with left join

cheeZer Published at Dev

cheeZer

I'm currently working on a simple employee scheduling tool and have a problem with a SQL query. To explain my problem let use this two very abstract tables.

The first table simply consists of the employees

employees
======================
empId | name    | ...
----------------------
10    | Scott   |
11    | Schrute |
12    | Halpert |
13    | Howard  |

In the second table you find the assigned tasks to each employee by day.

tasks
==============================================
tasId | name    | task     | date       | ...
----------------------------------------------
10    | Scott   | Support  | 2014-02-17 |
11    | Scott   | Bugfix   | 2014-02-18 |
12    | Halpert | Bugfix   | 2014-02-17 |
13    | Halpert | Develop  | 2014-02-18 |
14    | Howard  | Support  | 2014-02-17 |

Now I want to know what the employees are working on on Feb 17th or if they have no tasks planned for that day. I use the following SQL query to do that.

SELECT e.name, t.task
FROM employees e LEFT JOIN tasks t ON e.name = t.name
WHERE date IS NULL OR date = DATE('2014-02-17')

The result delivers exactly what I need:

name    | task
--------------------
Scott   | Support
Schrute | NULL
Halpert | Bugfix
Howard  | Support

And now to my problem. If I want to see the tasks of Feb 18th I get this result set:

name    | task
--------------------
Scott   | Bugfix
Schrute | NULL
Halpert | Develop

The reason to this is obvious, the date of Howard's tasks are neither NULL nor do they equal 2014-02-18.. What would be the best way to get the desired result?

I use MySQL and PHP.

(Sorry for the stupid title, I couldn't think of anything better..)

GarethD

Move your filter to the join predicate:

SELECT e.name, t.task
FROM employees e 
LEFT JOIN tasks t ON e.name = t.name AND t.date = DATE('2014-02-18');

Your query as it is will only return people who have no tasks at all, or have tasks on the date specified. It will omit people who have tasks, but not on the date specified. Consider the results of joining your sample data with no where clause:

empId | name    | task     | date       | ...
----------------------------------------|
10    | Scott   | Support  | 2014-02-17 |
10    | Scott   | Bugfix   | 2014-02-18 |
11    | Schrute | NULL     |   NULL     |
12    | Halpert | Bugfix   | 2014-02-17 |
12    | Halpert | Develop  | 2014-02-18 |
13    | Howard  | Support  | 2014-02-17 |

As you can see there is no record for Howard where Date = 2014-02-18, or where Date is null, this is why no record is returned for Howard. When you add the filter to the join predicate your results become:

empId | name    | task     | date       | ...
----------------------------------------|
10    | Scott   | Bugfix   | 2014-02-18 |
11    | Schrute | NULL     |   NULL     |
12    | Halpert | Develop  | 2014-02-18 |
13    | Howard  | NULL     |   NULL     |

Which I think is the desired results.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-02-6

Comments

0 comments

From Dev

Related Related

Article

Select rows from specific date with left join

Select rows from specific date with left join

SQL query with left join to get rows in decreasing order from a specific date

Select rows from table by id of a left join table

Select rows with a specific date

select from LEFT OUTER JOIN

left join same field select all rows

MySQL select rows where left join is null

Select rows with Left Outer Join and condition - MySQL

MySQL LEFT JOIN multiple rows from TableB

Get count of rows from LEFT JOIN

How to left_join() two datasets but only select specific columns from one of the datasets?

Left Join without duplicate rows from left table

Why LEFT OUTER JOIN reduces rows from left table?

LEFT join not returning all rows from LEFT table

Select data from two tables with LEFT JOIN

Left Join from Select Zend Framework

SELECT from different table and LEFT JOIN

Hide column from a left join select

MySQL - Select last rows of LEFT JOIN column based on the fields

Need to understand specific LEFT OUTER JOIN behavior in SQL SELECT

Select distinct rows from a table with an inner join

Select values from different rows in a mysql join

LEFT JOIN with a specific condition

Left Join on MAX(DATE)

Left Join on MAX(DATE)

Oracle, LEFT OUTER JOIN not returning all rows from left table, instead behaving like INNER JOIN

Left join without multiple rows from right table

Count rows from LEFT JOIN where column is value

Left join is not returning all rows from other table

Selecting next N rows from table with left join