mysql - 对两个 MySQL 查询执行左外连接？

Question

我在一个日程安排应用程序中有两个 SQL 查询来帮助检查员工是否有空。我需要执行左外连接，但我似乎无法完全正确地获得语法。

第一个查询获取在给定时间有空并具有执行要安排的任务的正确资格的员工列表。

    SELECT e.e_id FROM AppointmentTypes a1
    INNER JOIN EmployeeQualifications eq ON a1.type_id = eq.ea_id
    INNER JOIN Employees e ON e.e_id = eq.e_id
    INNER JOIN EmployeeAvaliable ea ON ea.e_id = e.e_id
    INNER JOIN EmployeeTimes et ON et.time_id = ea.time_id
    INNER JOIN AppointmentTimes a2
    INNER JOIN TypeTimes tt ON tt.time_id = a2.time_id AND tt.type_id = a1.type_id
    WHERE et.wednesday = '1'
    AND a1.type_id = '4'
    AND et.start <= '10:00:00' 
    AND et.end > '10:00:00'

第二个查询获取此时已安排事件的员工列表。

    SELECT e.e_id
    FROM AppointmentTypes atp 
    INNER JOIN Appointments a ON atp.type_id = a.type_id 
    INNER JOIN EmployeesAttending ea ON a.a_id = ea.a_id 
    INNER JOIN Employees e ON e.e_id = ea.e_id 
    WHERE date(a.start_time) = '2014-12-10'
    AND ADDTIME(time(a.start_time),'02:00:00') > '10:00:00'
    AND time(a.start_time) < '12:00:00'

Left outer joining these 应该给我一份合格的、有空的、当时还没有安排事件的员工名单。

Answer 1

没有左外连接会给你左边的所有东西加上右边的可以连接的东西。在他们不能的地方为空。

我认为你想要做的是获得第一组中不在第二组中的所有内容。

SELECT e.e_id FROM AppointmentTypes a1
INNER JOIN EmployeeQualifications eq ON a1.type_id = eq.ea_id
INNER JOIN Employees e ON e.e_id = eq.e_id
INNER JOIN EmployeeAvaliable ea ON ea.e_id = e.e_id
INNER JOIN EmployeeTimes et ON et.time_id = ea.time_id
INNER JOIN AppointmentTimes a2
INNER JOIN TypeTimes tt ON tt.time_id = a2.time_id AND tt.type_id = a1.type_id
WHERE et.wednesday = '1'
AND a1.type_id = '4'
AND et.start <= '10:00:00' 
AND et.end > '10:00:00'
AND e.e_id NOT IN (
    SELECT e.e_id
FROM AppointmentTypes atp 
INNER JOIN Appointments a ON atp.type_id = a.type_id 
INNER JOIN EmployeesAttending ea ON a.a_id = ea.a_id 
INNER JOIN Employees e ON e.e_id = ea.e_id 
WHERE date(a.start_time) = '2014-12-10'
AND ADDTIME(time(a.start_time),'02:00:00') > '10:00:00'
AND time(a.start_time) < '12:00:00'
)