sub query mysql

Question

If I have a table like this:

yr          subject  
1960    Physics
1960    Chemistry
1961    Physics
1962    Chemistry

I want all the years when the subject is Physics and not chemistry

If I try this , it does not give me correct result.

select yr from table where subject = "Physics" and  
yr NOT IN (select yr from table where subject = "Chemistry")

Answer 1

The problem with your query was that you were returning the years twice where two Physics awards were returned. Adding the DISTINCT keyword gives you just the years (as according question 3A on this site)

SELECT DISTINCT yr FROM nobel 
WHERE 
  subject = 'Physics' AND 
  yr NOT IN (SELECT yr FROM nobel WHERE subject = 'Chemistry');

Answer 2

Try this:

select yr from table t1 where subject = 'Physics'
where not exists
(select yr from table t2 where subject = 'Chemistry' and t1.yr = t2.yr)

Answer 3

This one solves the question stated by your link:

SELECT DISTINCT n1.yr 
FROM nobel n1
WHERE n1.subject = 'Physics'
AND n1.yr NOT IN (
  SELECT DISTINCT n2.yr
  FROM nobel n2
  WHERE n2.subject = 'Chemistry'
)

The reason this one works is because it removes duplicate years. Since a price can be awarded by multiple people in the same year and subject, you want to remove duplicates. You could also skip the DISTINCT of the inner query, which would skip one sorting step. In production you'd need to profile this query and create proper indexes.

Answer 4

The question at sqlzoo is a bit tricky since there is more than one physics prize for the year 1933. So, the correct answer is:

select distinct yr from nobel as n1
where subject = 'Physics' 
and not exists 
   (select 1 from nobel as n2 where n2.subject = 'Chemistry' and n2.yr = n1.yr)

You can check the 1933 results there with this query:

select * from nobel where yr = 1933

Answer 5

You are almost there...you need to use distinct:

select distinct yr from table where subject = "Physics" and  
yr NOT IN (select yr from table where subject = "Chemistry")

For the link given:

select distinct yr from nobel 
where subject = 'Physics' and 
yr not in (select yr from nobel where subject = 'Chemistry')