0

Just thought of learning SQL on my own and started with

THIS

I was practicing some tutorial and got stuck in one basic query.

After trying a lot I could not get it running.

Question is: in which years was the Physics prize awarded but no Chemistry prize.

Here is what I tried

SELECT yr 
FROM nobel
WHERE subject ='Physics' AND
NOT (Select yr from nobel where subject = 'Chemistry')

Can someone help.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Richa
  • 3,261
  • 2
  • 27
  • 51

7 Answers7

4
SELECT yr 
FROM nobel
WHERE subject ='Physics' AND
yr NOT IN (Select yr from nobel where subject = 'Chemistry')
Raj
  • 10,653
  • 2
  • 45
  • 52
2

So close! You just needed yr and IN:

SELECT yr 
FROM nobel
WHERE subject ='Physics' AND
    yr NOT IN (Select yr from nobel where subject = 'Chemistry')

You could also do this with an exclusion join:

SELECT yr
FROM nobel n1
LEFT JOIN nobel n2 ON n1.yr = n2.yr AND n2.subject = 'Chemistry'
WHERE n1.subject = 'Physics' AND n2.subject is NULL

or a NOT EXISTS

SELECT yr 
FROM nobel n1
WHERE n1.subject ='Physics' 
  AND NOT EXISTS 
  (
      SELECT NULL 
      FROM nobel n2 
      WHERE n2.subject = 'Chemistry' AND n2.yr=n1.yr
  )
Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
  • That is one detailed answer. Being noob will try to understand it :) But that was awesomely explained. Thank YOu – Richa Nov 26 '14 at 05:20
1

You could also use LEFT [OUTER] JOIN, to make sure that no row for the same year exists:

SELECT yr 
FROM   nobel p
LEFT   JOIN nobel c ON c.yr = p.yr
                   AND c.subject = 'Chemistry'
WHERE  p.subject = 'Physics'
AND    c.yr IS NULL;

There are basically 4 techniques:

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
0

I believe you don't need that second AND condition; cause with that second condition you are just trying to exclude all yr where subject is chemistry. Your query can simply be

SELECT yr 
FROM nobel
WHERE subject ='Physics'
Rahul
  • 76,197
  • 13
  • 71
  • 125
  • 1
    I don't think it should work. Because it will show even the year when Chemistry award was recieved. – Richa Nov 26 '14 at 05:19
0

Try This :

SELECT yr 
FROM nobel
WHERE subject ='Physics' AND yr
NOT IN(Select yr from nobel where subject = 'Chemistry') GROUP BY yr desc
kupendra
  • 1,002
  • 1
  • 15
  • 37
0

Just omit the

NOT (Select yr from nobel where subject = 'Chemistry')

since you are only looking for rows with Physics as subject. You won't need the NOT query since Chemistry will automatically be omitted by the sql query.

ronieldom
  • 1
  • 2
0
SELECT yr 
FROM nobel
WHERE subject ='Physics' AND subject <> 'Chemistry'

------------Or You can try the below---------------------------

SELECT yr 
FROM nobel
WHERE subject ='Physics'

except

SELECT yr 
FROM nobel
WHERE subject ='Chemistry'
bummi
  • 27,123
  • 14
  • 62
  • 101
Sudipto Maji
  • 41
  • 1