How do I query this sql table-CodePudding

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

CodePudding user response：

Try this query:

SELECT ID, Subject, Date 
FROM subject 
WHERE ID IN 
  (SELECT ID FROM subject 
   WHERE Subject = 'Maths' 
   GROUP BY ID HAVING 
   COUNT(Subject) = COUNT(*))

CodePudding user response：

You can try this query:

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