CODEWITHSUNDEEP
sqlsql-server
Richa
Richa

Reputation: 3289

Simple SELECT SQL query not working

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.

Upvotes: 0

Views: 160

Answers (7)

Sudipto Maji
Sudipto Maji

Reputation: 41

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'

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656461

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:

Upvotes: 1

ronieldom
ronieldom

Reputation: 1

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.

Upvotes: 0

Joel Coehoorn
Joel Coehoorn

Reputation: 415690

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
  )

Upvotes: 2

kupendra
kupendra

Reputation: 1003

Try This :

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

Upvotes: 0

Rahul
Rahul

Reputation: 77866

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'

Upvotes: 0

Raj
Raj

Reputation: 10843

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

Upvotes: 4

Related Questions