我需要一个JPA/SQL专家:内部联接上的EXISTS查询返回错误结果 [英] I need an JPA/SQL expert: EXISTS query on an Inner Join returns wrong result

问题描述

我有三个表，想要:

Select所有学生from第一张表

至少与第二张表中"999"区的学校有联系

that have at least one connection to the school in district '999' in the second table

and至少与社交编号为'101'的老师建立联系

and at least one connection to the teacher with social_number '101'

and在第三张表中至少有一个给编号为"103"的老师.

and at least one to the teacher with number '103' in the third table.

这些表通过第二个表连接.

The tables are connected through the second table.

我创建了一个在线sql编译器来显示问题: http://tpcg.io/FIoO79xi

I created an online sql compiler to show the problem: http://tpcg.io/FIoO79xi

此查询工作正常且符合预期，直到我在第三个EXISTS命令中搜索与教师'103'的连接为止.然后，它不再返回学生A，尽管他与"103"老师有联系

This query works fine and as expected, until I add the third EXISTS Command where I search for a connection to teacher '103'. Then it doesn't return student A anymore, altough he has a connection to teacher '103'

我通过在Exists sub-query中添加joins找到了解决方法: http://tpcg.io/0sza7t5g
但是由于我的实际数据库表中有数百万个条目，因此这将导致将sub-query所经过的每一行中的三个表连接起来，并且如果只在表的末尾找到合适的条目，这可能会花费很长的时间.

I found a workaround by adding joins in the Exists sub-query: http://tpcg.io/0sza7t5g
but since my real database tables have many million entries, this would lead to joining the three tables in every row that the sub-query goes through and this can take very long if it only finds a fitting entry at the end of the table.

我认为问题出在sub-query:WHERE th1.school_id = th.school_id，在这里我试图找到从第三位表老师到开始时连接在一起的表的连接.如果我搜索到与教师102而不是103的连接，则查询有效并返回学生A: http://tpcg.io/2tHIEk3V 因为老师101和102具有相同的school_id.

I think the problem is here at the sub-query: WHERE th1.school_id = th.school_id where I'm trying to find a connection from the third table teacher to the at the beginning joined together table. If I search for a connection to teacher 102 instead of 103, the query works and returns student A: http://tpcg.io/2tHIEk3V Because teacher 101 and 102 have the same school_id.

但是，当我搜索与老师101和103的连接时，如何用不同的方式写，以便查询也找到学生A?学生A与这两者都有联系，因此应该可以通过某种方式存在...

But how can I write that differently so that the query also finds student A when I search for a connection to teacher 101 and 103? Student A has a connection to both, so it should be possible somehow with exists...

添加:我不能使用三个单独的查询，然后在它们上使用Intersect命令，因为我正在将该SQL转换为JPA查询. JPA不知道相交...

Add: I can't use three seperate queries and then use the Intersect command on them, since I'm translating that SQL into a JPA query. JPAdoesn`t know intersect...

推荐答案

第一个条件:

至少第二个与"999"区的学校有联系 桌子

at least one connection to the school in district '999' in the second table

需要将student连接到school.
第二和第三条件:

needs a join of student to school.
The 2nd and 3rd conditions:

至少有一个与社交名"101"的老师联系
至少有一个给老师，编号为"103"

at least one connection to the teacher with social_number '101'
and at least one to the teacher with number '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个条件.
请参见演示.
结果:

Note that a condition like social_number in (101, 103) will not work because it would return results even if only 1 of the conditions was satisfied.
This is why you need 2 joins to school and teacher.
Also all the joins must be inner because you want to satisfy all 3 conditions.
See the demo.
Results:

| name |
| ---- |
| A    |

这篇关于我需要一个JPA/SQL专家:内部联接上的EXISTS查询返回错误结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！