Мне нужен эксперт JPA/SQL: запрос EXISTS во внутреннем соединении возвращает неправильный результат

У меня есть три таблицы и я хочу:

Select все студенты from первый стол,

которые имеют хотя бы одну связь со школой в округе «999» во второй таблице.

and хотя бы одно подключение к учителю с социальным_номером '101'

and хотя бы один учителю с номером «103» в третьей таблице.

Таблицы связаны через вторую таблицу.

мои три стола

Я создал онлайн-компилятор sql, чтобы показать проблему: http://tpcg.io/FIoO79xi

Этот запрос работает нормально и, как и ожидалось, пока я не добавлю третью команду EXISTS, где я ищу связь с учителем «103». Затем он больше не возвращает ученика А, хотя у него есть связь с учителем «103».

Я нашел обходной путь, добавив joins в Exists sub-query: http://tpcg.io/0sza7t5g
, но поскольку в моих реальных таблицах базы данных много миллионов записей, это приведет к объединению трех таблиц в каждой строке, через которую проходит sub-query, и это может занять очень много времени, если он найдет подходящую запись только в конце таблицы.

Я думаю, что проблема здесь, в sub-query: WHERE th1.school_id = th.school_id, где я пытаюсь найти связь между учителем за третьим столом и объединенным в начале столом. Если я ищу соединение с учителем 102 вместо 103, запрос работает и возвращает ученика A: http://tpcg.io/2tHIEk3V Потому что учителя 101 и 102 имеют одинаковый идентификатор school_id.

Но как я могу написать это по-другому, чтобы запрос также находил студента А, когда я ищу связь с учителем 101 и 103? У студента А есть связь с обоими, так что это должно быть как-то возможно с существующими...

Дополнение: я не могу использовать три отдельных запроса, а затем использовать для них команду Intersect, так как я перевожу этот SQL в запрос JPA. JPAне знает пересечения...


sql subquery jpa intersect exists
person G-Unit    schedule 18.03.2020    source источник
comment
Если вам не нужно решение SQL, вам нужен эксперт JPA, а не эксперт SQL.   -  person a_horse_with_no_name    schedule 18.03.2020
comment
Да, ха-ха, извини, ты прав.   -  person G-Unit    schedule 18.03.2020

Ответы (4)


arrow_upward
2
arrow_downward

1-е условие:

по крайней мере одно подключение к школе в округе «999» во второй таблице

требуется соединение student с school.
2-е и 3-е условия:

по крайней мере одно соединение с учителем с номером social_number '101'
и по крайней мере одно соединение с учителем с номером '103'

нужно 2 отдельных соединения student с school и teacher:

SELECT s.name 
FROM student s
INNER JOIN school sc on s.student_id = sc.student_id AND sc.district = 999
INNER JOIN school sc1 on s.student_id = sc1.student_id
INNER JOIN teacher t1 on t1.school_id = sc1.school_id AND t1.social_number = 101
INNER JOIN school sc2 on s.student_id = sc2.student_id
INNER JOIN teacher t2 on t2.school_id = sc2.school_id AND t2.social_number = 103

Обратите внимание, что такое условие, как social_number in (101, 103), не будет работать, потому что оно вернет результаты, даже если будет выполнено только одно из условий.
Вот почему вам нужно 2 соединения с school и teacher.
Также все соединения должны быть inner, потому что вы хотите удовлетворить все 3 условия.
См. демо.
Результаты:

| name |
| ---- |
| A    |
person forpas    schedule 18.03.2020
comment
Отличный ответ. Это должно работать отлично и выглядит более аккуратно, чем использование EXISTS. - person COY; 18.03.2020
comment
Это здорово спасибо! Когда я увидел ваш ответ, я подумал, что, возможно, для нашей базы данных слишком много соединений, а затем нашел другое решение с коллегой, но завтра я попробую ваше решение в нашей базе данных, может быть, оно быстрее. - person G-Unit; 18.03.2020
comment
Это решение было быстрее, чем решение с существующими подзапросами в нашей базе данных. - person G-Unit; 25.03.2020

arrow_upward
1
arrow_downward

Вам нужно 2 присоединения к учительскому столу

SELECT name 
FROM student
left JOIN school sc1 on #student.student_id = sc1.student_id
left JOIN teacher th1 on sc1.school_id = th1.school_id and th1.social_number=101
left JOIN teacher th2 on sc1.school_id = th2.school_id and th1.social_number=103
where sc1.district=999
person Atk    schedule 18.03.2020
comment
Кажется, это работает, спасибо. Завтра я попробую рабочие ответы и посмотрю, какой из них быстрее в нашей базе данных. - person G-Unit; 18.03.2020

arrow_upward
0
arrow_downward

Почему так сложно?

`SELECT name 
FROM student
LEFT JOIN school sc1 on student.student_id = sc1.student_id
LEFT JOIN teacher th1 on sc1.school_id = th1.school_id
WHERE sc1.district = '999'
AND th1.social_number in('101','103')`

не делает трюк?

person Loïc Di Benedetto    schedule 18.03.2020
comment
Это не сработает, потому что матч учителя не совпадает со школьным матчем ... Я думаю, это именно то, что пытался сделать ОП. - person COY; 18.03.2020
comment
спасибо, но, как сказал COY, это не сработает так, как я планировал... - person G-Unit; 18.03.2020

arrow_upward
0
arrow_downward

http://tpcg.io/eGeWjkOf

Во всем соединении нет необходимости, когда мы просим установить связь между учителем и учеником в дополнительном предложении WHERE. Затем Exists subquery выглядит следующим образом: вместо Join я использую дополнительный where, чтобы убедиться, что Teacher.school_id совпадает с school.school_id школы, в которую ходит ученик:

EXISTS (
SELECT *
FROM teacher th
WHERE th.social_number = '103'
AND th.school_id in (SELECT school_id FROM school WHERE student_id = student.student_id ))
person G-Unit    schedule 18.03.2020