Short SQL Query Question

Table name = nobel
yr subject winner
1960 Chemistry Willard F. Libby
1960 Literature Saint-John Perse
1960 Medicine Sir Frank Macfarlane Burnet
1960 Medicine Peter Medawar

I need to find : In which years was the Physics prize awarded but no Chemistry prize?

Here's what I have


select yr from nobel where yr NOT IN(select yr from nobel where subject='Chemistry') AND subject='Physics'


Why is it incorrect?!

I need to find : In which years was the Physics prize awarded but no Chemistry prize?
There's no "Physics prize" in the any rows given?

I only gave a sample of the table. The table contains the winner, subject, and year. The subject columns indicate the prize.

Your query worked properly for me.


mysql> select * from nobel;
+------+-----------+
| yr | subject |
+------+-----------+
| 1960 | Physics |
| 1960 | Chemistry |
| 1964 | Physics |
| 1968 | Physics |
| 1968 | Chemistry |
| 1972 | Physics |
+------+-----------+
6 rows in set (0.00 sec)

mysql> select yr from nobel where yr NOT IN(select yr from nobel
where subject='Chemistry') AND subject='Physics';
+------+
| yr |
+------+
| 1964 |
| 1972 |
+------+
2 rows in set (0.00 sec)


Maybe your data is wrong??

resolved! There are still duplicate years, I needed to add distinct. Thanks for your help!