mysql - 从满足条件但不满足条件的表中选择

我有3张桌子：

Students
(student_id, name, group, year, specialization)

Scolarships
(scolarship_id, name, description, duration)

Applicants
(student_id, scolarship_id)

我需要选择所有已申请id=2奖学金但尚未申请id=4奖学金的学生

到目前为止，我有此查询：

select students.name, students.group, students.specialization
from applicants ap
inner join students on students.id = ap.student_id
inner join scolarships on scolarships.scolarship_id = ap.scolarship_id
where ap.scolarship_id = 2;

这将选择所有申请id = 2的奖学金的学生。

我如何添加他们已申请奖学金2但未申请奖学金4的条件？

最佳答案

您可以使用not exists作为

select students.name, students.group, students.specialization
from applicants ap
inner join students on students.id = ap.student_id
inner join scolarships on scolarships.scolarship_id = ap.scolarship_id
where
ap.scolarship_id = 2
and not exists(
 select 1 from applicants ap1
 where
 ap.student_id = ap1.student_id
 and ap1.scolarship_id = 4
)
;

关于mysql - 从满足条件但不满足条件的表中选择，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/31045306/