...

View Full Version : Short SQL Query Question



Ahlahn
06-03-2011, 03:42 PM
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?!

abduraooft
06-03-2011, 03:51 PM
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?

Ahlahn
06-03-2011, 03:53 PM
I only gave a sample of the table. The table contains the winner, subject, and year. The subject columns indicate the prize.

Old Pedant
06-03-2011, 08:25 PM
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??

Ahlahn
06-04-2011, 02:39 AM
resolved! There are still duplicate years, I needed to add distinct. Thanks for your help!



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum