CODEWITHSUNDEEP
mysqldatabase
GeR
GeR

Reputation: 135

Select from a table where a condition is fulfilled and another is not

I have 3 tables:

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

Scolarships
(scolarship_id, name, description, duration)

Applicants
(student_id, scolarship_id)

I need to select all the students that have applied for scolarship with id=2 and have not applied for scolarship with id=4

So far I have this query:

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;

This selects all the students that have applied for scolarship with id=2.

How can I add the condition that they have applied for scolarship 2 but haven't applied for scolarship 4?

Upvotes: 0

Views: 85

Answers (2)

Madhivanan
Madhivanan

Reputation: 13700

Try this

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)

Upvotes: 1

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44864

You can use not exists something as

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
)
;

Upvotes: 0

Related Questions