mysql - 从表中选择一个满足条件而另一个不满足的条件

Question

我有 3 张 table :

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 的条件？

Answer 1

试试这个

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 * from applicants as t1
where t1.student_id = students.student_id and t1.scolarship_id = 4)