on 2016 Jan 05 3:27 AM
Hi
I would like to formulate an SQL Statement like
SELECT x FROM a EXCEPT SELECT x FROM b RIGHT OUTER JOIN table_n ON ... WHERE ...
but can't seem to find the correct syntax. Goal is to first subtract b from a and then outer join it.
I also tried to write it as
SELECT a.x FROM a LEFT OUTER JOIN b ON b.x = a.x WHERE b.x IS NULL RIGHT OUTER JOIN table_n ON ... WHERE ...
with no success.
Any hint is welcome. Regards, Robert
The EXCEPT set operator (like UNION and INTERSECT) works on whole query blocks so it has lower precedence than a join operator.
Try to enclose the EXCEPT statement within braces and use it as a derived query and join it then, such as
SELECT [yourSelectListHere] FROM (SELECT x FROM a EXCEPT SELECT x FROM b) dt RIGHT OUTER JOIN table_n ON dt.x = table_n... WHERE ...
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
> turns an OUTER join into an INNER join
@ Volker: Your suggestion could very well be the reason. The number of records (of the SELECT in the RIGHT OUTER JOIN) returned for the given example is correct when executed separately, which could hint in your direction.
@Breck: Thanks for your list.
The mentioned SQL statement looks like this (HS means Herbst-Semester):
SELECT DISTINCT o1.day_number, o1.start_time, o1.end_time, o1.untis_lesson_subject, o1.untis_lesson_teacher, o1.untis_assigned_room FROM ( SELECT w.day_number, lt.start_time, lt.end_time, tl.untis_lesson_subject, tl.untis_lesson_teacher, tr.untis_assigned_room FROM timetable_lessons tl INNER JOIN timetable_positions tp ON tp.timetable_lesson_id = tl.id INNER JOIN timetable_rooms tr ON tr.timetable_position_id = tp.id INNER JOIN timetable_classes tc ON tc.timetable_lesson_id = tl.id AND tc.class_level_number = 3 AND tc.class_sign = 'ma' INNER JOIN profile_positions pp ON pp.profile_definition_subject_code = tl.subject_code AND pp.person_id = 15789 AND pp.period_school_year = 2015 AND pp.period_fraction_name = 'HS' RIGHT OUTER JOIN lesson_times lt ON lt.start_time = tp.untis_assigned_start_time AND tp.day = lt.weekday_id AND tl.period_school_year = 2015 AND tl.period_fraction_name = 'HS' AND tp.week_number = 51 INNER JOIN weekdays w ON lt.weekday_id = w.id EXCEPT SELECT w.day_number, lt.start_time, lt.end_time, tl.untis_lesson_subject, tl.untis_lesson_teacher, tr.untis_assigned_room FROM timetable_lessons tl INNER JOIN timetable_positions tp ON tp.timetable_lesson_id = tl.id INNER JOIN timetable_rooms tr ON tr.timetable_position_id = tp.id INNER JOIN timetable_classes tc ON tl.id = tc.timetable_lesson_id AND tc.class_level_number = 3 AND tc.class_sign = 'ma' INNER JOIN partial_class_courses pcc ON pcc.timetable_lesson_code = tl.untis_lesson_subject INNER JOIN partial_class_course_members pccm ON pccm.partial_class_course_name = pcc.name AND pccm.partial_class_course_name IN (SELECT pccm.partial_class_course_name FROM partial_class_course_members pccm WHERE pccm.class_member_class_period_school_year = 2015 AND pccm.class_member_class_period_fraction_name = 'HS' AND pccm.partial_class_course_class_course_class_level_number = 3 AND pccm.partial_class_course_class_course_class_sign = 'ma' EXCEPT SELECT pccm.partial_class_course_name FROM partial_class_course_members pccm WHERE pccm.class_member_person_id = 15789 AND pccm.class_member_class_period_school_year = 2015 AND pccm.class_member_class_period_fraction_name = 'HS' AND pccm.partial_class_course_class_course_class_level_number = 3 AND pccm.partial_class_course_class_course_class_sign = 'ma') AND pccm.partial_class_course_class_course_class_period_school_year = 2015 AND pccm.partial_class_course_class_course_class_period_fraction_name = 'HS' INNER JOIN lesson_times lt ON lt.start_time = tp.untis_assigned_start_time AND tp.day = lt.weekday_id AND tl.period_school_year = 2015 AND tl.period_fraction_name = 'HS' AND tp.week_number = 51 INNER JOIN weekdays w ON lt.weekday_id = w.id) o1 RIGHT OUTER JOIN ( SELECT lt.start_time FROM timetable_lessons tl INNER JOIN timetable_positions tp ON tp.timetable_lesson_id = tl.id INNER JOIN timetable_rooms tr ON tr.timetable_position_id = tp.id INNER JOIN timetable_classes tc ON tc.timetable_lesson_id = tl.id AND tc.class_level_number = 3 AND tc.class_sign = 'ma' INNER JOIN profile_positions pp ON pp.profile_definition_subject_code = tl.subject_code AND pp.person_id = 15789 AND pp.period_school_year = 2015 AND pp.period_fraction_name = 'HS' RIGHT OUTER JOIN lesson_times lt ON lt.start_time = tp.untis_assigned_start_time AND tp.day = lt.weekday_id AND tl.period_school_year = 2015 AND tl.period_fraction_name = 'HS' AND tp.week_number = 51 INNER JOIN weekdays w ON lt.weekday_id = w.id) x ON x.start_time = o1.start_time ORDER BY o1.day_number ASC, o1.start_time ASC, o1.untis_lesson_subject ASC, o1.untis_lesson_teacher ASC;
Thanks, Volker, here is the formatted SQL statement
SELECT DISTINCT o1.day_number, o1.start_time, o1.end_time, o1.untis_lesson_subject, o1.untis_lesson_teacher, o1.untis_assigned_room FROM ( SELECT w.day_number, lt.start_time, lt.end_time, tl.untis_lesson_subject, tl.untis_lesson_teacher, tr.untis_assigned_room FROM timetable_lessons tl INNER JOIN timetable_positions tp ON tp.timetable_lesson_id = tl.id INNER JOIN timetable_rooms tr ON tr.timetable_position_id = tp.id INNER JOIN timetable_classes tc ON tc.timetable_lesson_id = tl.id AND tc.class_level_number = 3 AND tc.class_sign = 'ma' INNER JOIN profile_positions pp ON pp.profile_definition_subject_code = tl.subject_code AND pp.person_id = 15789 AND pp.period_school_year = 2015 AND pp.period_fraction_name = 'HS' RIGHT OUTER JOIN lesson_times lt ON lt.start_time = tp.untis_assigned_start_time AND tp.day = lt.weekday_id AND tl.period_school_year = 2015 AND tl.period_fraction_name = 'HS' AND tp.week_number = 51 INNER JOIN weekdays w ON lt.weekday_id = w.id EXCEPT SELECT w.day_number, lt.start_time, lt.end_time, tl.untis_lesson_subject, tl.untis_lesson_teacher, tr.untis_assigned_room FROM timetable_lessons tl INNER JOIN timetable_positions tp ON tp.timetable_lesson_id = tl.id INNER JOIN timetable_rooms tr ON tr.timetable_position_id = tp.id INNER JOIN timetable_classes tc ON tl.id = tc.timetable_lesson_id AND tc.class_level_number = 3 AND tc.class_sign = 'ma' INNER JOIN partial_class_courses pcc ON pcc.timetable_lesson_code = tl.untis_lesson_subject INNER JOIN partial_class_course_members pccm ON pccm.partial_class_course_name = pcc.name AND pccm.partial_class_course_name IN (SELECT pccm.partial_class_course_name FROM partial_class_course_members pccm WHERE pccm.class_member_class_period_school_year = 2015 AND pccm.class_member_class_period_fraction_name = 'HS' AND pccm.partial_class_course_class_course_class_level_number = 3 AND pccm.partial_class_course_class_course_class_sign = 'ma' EXCEPT SELECT pccm.partial_class_course_name FROM partial_class_course_members pccm WHERE pccm.class_member_person_id = 15789 AND pccm.class_member_class_period_school_year = 2015 AND pccm.class_member_class_period_fraction_name = 'HS' AND pccm.partial_class_course_class_course_class_level_number = 3 AND pccm.partial_class_course_class_course_class_sign = 'ma') AND pccm.partial_class_course_class_course_class_period_school_year = 2015 AND pccm.partial_class_course_class_course_class_period_fraction_name = 'HS' INNER JOIN lesson_times lt ON lt.start_time = tp.untis_assigned_start_time AND tp.day = lt.weekday_id AND tl.period_school_year = 2015 AND tl.period_fraction_name = 'HS' AND tp.week_number = 51 INNER JOIN weekdays w ON lt.weekday_id = w.id) o1 RIGHT OUTER JOIN ( SELECT lt.start_time FROM timetable_lessons tl INNER JOIN timetable_positions tp ON tp.timetable_lesson_id = tl.id INNER JOIN timetable_rooms tr ON tr.timetable_position_id = tp.id INNER JOIN timetable_classes tc ON tc.timetable_lesson_id = tl.id AND tc.class_level_number = 3 AND tc.class_sign = 'ma' INNER JOIN profile_positions pp ON pp.profile_definition_subject_code = tl.subject_code AND pp.person_id = 15789 AND pp.period_school_year = 2015 AND pp.period_fraction_name = 'HS' RIGHT OUTER JOIN lesson_times lt ON lt.start_time = tp.untis_assigned_start_time AND tp.day = lt.weekday_id AND tl.period_school_year = 2015 AND tl.period_fraction_name = 'HS' AND tp.week_number = 51 INNER JOIN weekdays w ON lt.weekday_id = w.id) x ON x.start_time = o1.start_time ORDER BY o1.day_number ASC, o1.start_time ASC, o1.untis_lesson_subject ASC, o1.untis_lesson_teacher ASC;
Given
I would try to simplify the select list to just return just these two columns - that way you may be able to check whether all according rows are listed and which ones do have NULL on the left side (alias o1).
And as your real select list only returns DISTINCT rows from the NULL-supplying side o1, that may strip away some rows from the right-hand side, in case they have duplicate values or are all NULL - please note that DISTINCT treats two NULL values as identical.
(Note, I do not claim to understand that complex statement, it's just a distant observation...).
User | Count |
---|---|
68 | |
8 | |
8 | |
6 | |
6 | |
6 | |
6 | |
6 | |
6 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.