CODEWITHSUNDEEP
sql
John
John

Reputation: 99

How do I query this SQL table

I am learning the SQL query and I have student subject table as below

ID Subject Date
One English 30/01/2023
One Science 28/01/2023
One Maths 30/01/2023
One Maths 29/01/2023
Two Maths 30/01/2023
Two Maths 29/01/2023
Three Maths 30/01/2023
Four Science 30/01/2023
Four Maths 30/01/2023

Output:

I need to retrieve the IDs which contains only Maths subject as records as below

ID Subject Date
Two Maths 30/01/2023
Two Maths 29/01/2023
Three Maths 30/01/2023

I tried the below but it retrieves the ID 1 and 4 also.

select * 
from subject 
where subject in ('Maths')

Upvotes: 0

Views: 79

Answers (2)

Khalil
Khalil

Reputation: 557

Try this query:

SELECT ID, Subject, Date 
FROM subject 
WHERE ID IN 
  (SELECT ID FROM subject 
   GROUP BY ID HAVING 
   count(case when Subject='Maths' then 1 end) = count(*))

Upvotes: 2

s.soltanzadeh
s.soltanzadeh

Reputation: 126

You can try this query:

select * from subject 
where ID not in (select ID from subject where subject<>'Maths')

Upvotes: 4

Related Questions