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
  • 704,970
  • 168
  • 1,303
  • 1,425
Richa
  • 3,205
  • 1
  • 23
  • 48

7 Answers7

4
SELECT yr 
FROM nobel
WHERE subject ='Physics' AND
yr NOT IN (Select yr from nobel where subject = 'Chemistry')
Raj
  • 10,433
  • 2
  • 43
  • 50
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
  • 380,066
  • 110
  • 546
  • 781
  • 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
  • 539,169
  • 125
  • 977
  • 1,137
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
  • 73,987
  • 13
  • 62
  • 116
  • 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
  • 35
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
  • 26,839
  • 13
  • 60
  • 97
Sudipto Maji
  • 41
  • 1