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 asked 05 Jan '16, 03:27 robert Volker Barth |
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 ... answered 05 Jan '16, 04:05 Volker Barth Thanks, Volker, I use it exactly that way but somehow I do not get the number of rows the RIGHT OUTER JOIN table delivers. So I have to continue to analyse it.
(05 Jan '16, 12:38)
robert
Replies hidden
What does the WHERE clause look like? I'm asking because it is not uncommon that a condition in the WHERE clause relates to the NULL-supplying side (here the left side) and turns an OUTER join into an INNER join - been there, too...
(05 Jan '16, 16:29)
Volker Barth
> turns an OUTER join into an INNER join
(05 Jan '16, 16:45)
Breck Carter
@ 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;
(06 Jan '16, 02:19)
robert
Comment Text Removed
Comment Text Removed
Sorry, the formatting of my SQL statement get's lost when I press the comment button. Is there a way the formatting is kept? The formatting is there when I copy/paste it into the comment field.
(06 Jan '16, 02:28)
robert
Replies hidden
I forgot to mention I have no WHERE clause.
(06 Jan '16, 02:31)
robert
Just insert a pre tag before the code. - I tried to but the text seems a few chars too long for one comment, so you might split up the comments and the statement code into two comments...
(06 Jan '16, 04:10)
Volker Barth
Comment Text Removed
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;
(06 Jan '16, 04:58)
robert
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...).
(06 Jan '16, 06:13)
Volker Barth
You can (almost) always use raw HTML tags as well as the funky formatting thingies; e.g., B and /B instead of two asterisks. In this case, I added PRE tags.
(06 Jan '16, 07:42)
Breck Carter
Or, to put it another way, I'd suggest to add x.start_time to the final SELECT list to find out which result set rows would have duplicate or all-NULL values in the colums of derived table o1. - That would be the probably missing rows when x.start_time is not part of the final SELECT list.
(06 Jan '16, 14:56)
Volker Barth
We built up the statement once again from the beginning and it works now as expected. Thanks for all the helpful tips I got from you all - I very much appreciate it!
(08 Jan '16, 03:02)
robert
Glad to hear that - feel free to check that answer as accepted, if you like:)
(08 Jan '16, 04:07)
Volker Barth
More comments hidden
|