在多个表上有多个JOIN的SQL查询
问题描述:
我创建了这个查询来匹配多个表的数据,但我觉得它有一点复杂。在多个表上有多个JOIN的SQL查询
我需要从下表中选择:
leads.sequence = instructors_options.instructor_seq
leads.sequence = instructor_calendar.instructor_seq
和:
- 使用下面的关系导致
- instructors_options
- instructor_calendar
以下过滤器必须匹配:
- lead.lead_type ='辅导员
- instructors_options.option_name = 'pupil_cap' AND instructors_options.value> 0
- instructors_options.option_name = 'car_type' AND instructors_options.value ='变量”
- instructors_options.option_name = 'areas_covered' AND instructors_options.value = '变量'
- instructor_calendar.pupil_name = '可用' AND instructor_calendar.date> = '变量' AND instructor_calendar.date < = '变量'
-
instructors_options.option_name = 'diary_updates' AND instructors_options.value = '1'
SELECT i.sequence as instructor_seq, ic.date AS date, ic.start_time AS start_time, ic.end_time AS end_time FROM leads i LEFT JOIN instructors_options io ON i.sequence = io.instructor_seq AND (io.option_name = 'pupil_cap' AND io.value > '0') RIGHT JOIN instructors_options io2 ON i.sequence = io2.instructor_seq AND io2.option_name = 'car_type' AND io2.value = '".$bookingData["car_type"]."' RIGHT JOIN instructors_options io3 ON i.sequence = io3.instructor_seq AND io3.option_name = 'areas_covered' AND (io3.value LIKE '".substr($postcode, 0, 1)."' OR io3.value LIKE '".substr($postcode, 0, 2)."' OR io3.value LIKE '".substr($postcode, 0, 3)."' OR io3.value LIKE '".substr($postcode, 0, 4)."') RIGHT JOIN instructors_options io4 ON i.sequence = io4.instructor_seq AND io4.option_name = 'diary_updates' AND io4.value = '1' RIGHT JOIN instructor_calendar ic ON i.sequence = ic.instructor_seq AND ic.pupil_name = 'AVAILABLE' AND ic.date >= '".ChangeDateFormat($date, 'Y-m-d')."' AND ic.date <= '".date('Y-m-d', strtotime($date. ' + 7 days'))."' WHERE i.lead_type = 'Instructor' GROUP BY date ORDER BY date ASC, start_time ASC
有人可以帮我更新我的查询,以确保我已经正确地做了
谢谢!
答
您正在查找某些日历条目。因此请从该表中选择。
其中一个条件是相关的教师序列具有全部四个特定选项。因此,按照教师序列汇总选项并保留符合所有条件的选项。使用IN
子句获取这些教师序列的日历条目。
select
instructor_seq,
date,
start_time,
end_time
from instructor_calendar
where pupil_name = 'AVAILABLE'
and date >= '2017-01-01'
and date <= '2017-05-31'
and instructor_seq in
(
select instructor_seq
from instructors_options
group by instructor_seq
having sum(option_name = 'pupil_cap' and value > 0) > 0
and sum(option_name = 'car_type' and value = 123) > 0
and sum(option_name = 'areas_covered' and value = 456) > 0
and sum(option_name = 'diary_updates' and value = 1) > 0
);
的HAVING
条款的方式利用MySQL的true = 1
,false = 0
的。
这是MySQL问题还是SQLServer问题?这些是两种非常不同的产品,并且你们都标记了它们。为什么? – pmbAustin
对不起 - 我的错误,必须无意中点击sql-server – charlie
我注意到的第一件事是你正在寻找“必须”匹配,但有左和右连接。你确定你不想要INNER JOIN吗?你可以有额外的加入(io4),或者你错过了需求清单:) –