CODEWITHSUNDEEP
sql
user3623932
user3623932

Reputation:

Selecting from same table, excluding one select

My table is: nobelpeace(year, sub, win)
I need: In which years was the English prize awarded but no Physics prize. I tried:

SELECT DISTINCT year
FROM nobelpeace
WHERE sub='english' NOT IN 
  (SELECT DISTINCT year
   FROM nobelpeace
   WHERE sub='physics')

Any idea how to make this work?

Upvotes: 0

Views: 79

Answers (5)

mrdeveloper
mrdeveloper

Reputation: 538

Simple Way

select DISTINCT year from nobel
WHERE
subject='Physics'AND year
NOT IN
( select year from nobel
WHERE
subject = 'Chemistry')

Demo: http://sqlfiddle.com/#!2/8ab2f/15

Upvotes: 0

qxg
qxg

Reputation: 7036

SELECT yr
FROM nobel
WHERE subject = 'physics'
EXCEPT
SELECT yr
FROM nobel
WHERE subject = 'chemistry'

EXCEPT will do DISTINCT for you.

Upvotes: 0

Serpiton
Serpiton

Reputation: 3684

Your query will work, if you tell SQLServer what value should not be in the result of the subquery.

SELECT DISTINCT yr
FROM   nobel
WHERE  subject = 'physics'
  AND  yr NOT IN (SELECT DISTINCT yr
                  FROM nobel
                  WHERE subject='chemistry')

Upvotes: 1

juergen d
juergen d

Reputation: 204746

Group by the year and sum how many times these prizes were awarded. 

SELECT  yr
FROM nobel
group by yr
having sum(case when subject='physics' then 1 else 0 end) > 0
and sum(case when subject='chemistry' then 1 else 0 end) = 0

The above query works in all SQL engines. If you use MySQL you can do it even a little shorter like this

SELECT  yr
FROM nobel
group by yr
having sum(subject='physics') > 0
and sum(subject='chemistry') = 0

Upvotes: 0

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

Another way:

SELECT DISTINCT yr
FROM nobel N1
WHERE subject = 'physics'
  AND NOT EXISTS
(
    SELECT 1 FROM nobel N2
    WHERE N1.yr = N2.yr
      AND N2.sublect = 'chemistry'
)

Upvotes: 0

Related Questions