I have a problem with a MySQL query.
Here are my three tables:
employees:
id initials deleted
------ -------- ---------
1 TV 0
2 AH 0
3 JE 0
4 MA 0
5 MJ 0
6 CE 0
7 KB 1
8 KL 1
Schedule :
id place start end deleted
------ -------------- ------------------- ------------------- ---------
1 Somewhere 2018-05-11 16:48:17 2018-05-15 16:48:26 0
2 Somewhere else 2018-05-12 16:48:50 2018-05-14 16:48:55 1
3 Here 2018-05-13 00:00:00 2018-05-13 00:00:00 0
4 Not here 2018-05-18 16:49:42 2018-05-16 16:49:48 0
And schedule_link :
id id_employee id_schedule
------ ----------- -------------
1 1 1
2 1 2
3 4 3
4 5 4
I would like a request that returns all that is concerned by the date of the day for each employee. Even if the employee does not have any records found, I would like the query to return its initials with NULL in the other columns.
Here is my current query:
SELECT
`employees`.`id`,
`employees`.`initials`,
`schedule`.`place`,
`schedule`.`start`,
`schedule`.`end`,
`schedule`.`deleted`
FROM
`employees`
LEFT JOIN `schedule_link`
ON (
`employees`.`id` = `schedule_link`.`id_employee`
)
LEFT JOIN `schedule`
ON (
`schedule_link`.`id_schedule` = `schedule`.`id`
)
WHERE (
`employees`.`deleted` = '0'
AND `schedule`.`deleted` = '0'
)
AND (
DATE(CURDATE()) BETWEEN CAST(schedule.start AS DATE)
AND CAST(schedule.end AS DATE)
)
This returns me the following data:
id initials place start end deleted
------ -------- --------- ------------------- ------------------- ---------
1 TV Somewhere 2018-05-11 16:48:17 2018-05-15 16:48:26 0
4 MA Here 2018-05-13 00:00:00 2018-05-13 00:00:00 0
It's correct, but what I want is the following result:
id initials place START END deleted
------ -------- --------- ------------------- ------------------- ---------
1 TV Somewhere 2018-05-11 16:48:17 2018-05-15 16:48:26 0
4 MA Here 2018-05-13 00:00:00 2018-05-13 00:00:00 0
2 AH NULL NULL NULL 0
3 JE NULL NULL NULL 0
5 MJ NULL NULL NULL 0
6 CE NULL NULL NULL 0
Is it possible to obtain this result with a single request?
Thank you in advance for your help.
You need to put the conditions on all but the first table in the on clause. Your where clause is turning the outer join into an inner join.
I have some other suggestions:
SELECT e.id, e.initials, s.place, s.start, s.end, s.deleted
FROM employees e LEFT JOIN
schedule_link sl
ON e.id = sl.id_employee LEFT JOIN
schedule s
ON sl.id_schedule = s.id AND s.deleted = 0 AND
CURDATE() BETWEEN CAST(s.start AS DATE) AND CAST(s.end AS DATE)
WHERE e.deleted = 0;
Notes:
start and end as column names (i.e., rename them if you can). They are keywords (although not reserved), so they have other purposes in a SQL statement.deleted is numeric. Don't use single quotes for the comparison (unless the column is really a string).CURDATE() is already a date. No need for conversion.BETWEEN with dates, because of the possibility of a lingering time component. However, you are using explicit conversions, so the code unambiguously does what you want (at the risk perhaps of not using an available index).EDIT:
I see. Because the date condition is in the third table, not the second, you are getting duplicate rows. I think this will fix your problem:
SELECT e.id, e.initials, ss.place, ss.start, ss.end, ss.deleted
FROM employees e LEFT JOIN
(SELECT sl.id_employee, s.*
FROM schedule_link sl JOIN
schedule s
ON sl.id_schedule = s.id AND s.deleted = 0
WHERE CURDATE() BETWEEN CAST(s.start AS DATE) AND CAST(s.end AS DATE)
) ss
ON e.id = ss.id_employee
WHERE e.deleted = 0;
This will include every employee with no match on the time frame exactly once. You will still get every record from schedule if there are multiple matches.
You can actually express this without a subquery:
SELECT e.id, e.initials, s.place, s.start, s.end, s.deleted
FROM employees e LEFT JOIN
(schedule_link sl JOIN
schedule s
ON sl.id_schedule = s.id AND s.deleted = 0 AND
CURDATE() BETWEEN CAST(s.start AS DATE) AND CAST(s.end AS DATE)
)
ON e.id = sl.id_employee
WHERE e.deleted = 0;
I find the subquery version easier to follow.