我有一个员工表,他们都可以在多个部门工作。但是,一旦预订了该日期的某个部分,就无法在该日期的其他部分中预订
员工表是员工
id_resources id_sections EmployeeName Section
28 25 Mark Section Two
28 26 Mark Section Three
28 28 Mark Section Four
28 29 Mark Section Five
28 53 Mark Section Six
28 64 Mark Section Seven
28 74 Mark Section Eight
28 76 Mark Section Nine
29 30 Blair Section One
29 47 Blair Section Two
29 49 Blair Section Three
30 82 Sam Section One
30 25 Sam Section Two
30 28 Sam Section Three
30 29 Sam Section Four
31 18 David Section Eleven
31 19 David Section One
31 22 David Section Two
31 25 David Section Three
那么我的预订表是预订
id_resources id_sections startdate enddate
28 25 2016-09-09 2016-09-09
28 26 2016-09-20 2016-09-20
28 28 2016-10-17 2016-10-17
30 82 2016-10-18 2016-10-18
30 25 2016-10-28 2016-10-28
29 30 2016-11-02 2016-11-02
29 47 2016-11-07 2016-11-07
29 49 2016-11-01 2016-11-01
29 58 2017-01-09 2017-01-09
所以我遇到的问题是让员工成为:
A.( 如果一个部分预订了一天
,则不适用于所有部分 B.)当选择了日期范围并且没有该范围的预订时,将员工显示为有空。
我可以得到 A 部分,但不能得到 B 部分。
Select *
FROM employees
LEFT JOIN bookings on employees.id_resources = bookings.id_resources
WHERE
(Select..... and i get lost here
再次感谢您的任何帮助!
-李
你应该尝试这样的事情:
SELECT employees.*
FROM employees
LEFT OUTER JOIN bookings
ON employees.id_resources = bookings.id_resources
AND (
(startdate BETWEEN $start_range AND $end_range)
OR
(enddate BETWEEN $start_range AND $end_range)
)
WHERE bookings.id_resources IS NULL
快速解释:我们使用左外连接...哪里。。。为空作为排除。这篇文章可能会对你有所帮助
编辑 :当您使用左外部连接作为排除时,首先,您必须考虑与目标相反的思维。在这里,它将找到一个在两个日期之间预订的用户。为此,您将有一个如下所示的查询:
SELECT employees.*
FROM employees
INNER JOIN bookings
ON employees.id_resources = bookings.id_resources
AND (startdate BETWEEN $start_range AND $end_range)
AND (enddate BETWEEN $start_range AND $end_range)
然后你做相反的事情。