CODEWITHSUNDEEP
mysqlsqlsubquery
jantristanmilan
jantristanmilan

Reputation: 4368

Whats wrong with my nested subquery?

So I'm solving the interactive tutorial http://www.sqlzoo.net/wiki/SELECT_from_Nobel_Tutorial However the bonus part doesn't have a solution.

Here's the question: In which years was the Physics prize awarded but no Chemistry prize.

SELECT yr FROM nobel
WHERE subject = 'Physics' AND yr NOT IN
(SELECT yr FROM nobel WHERE subject = 'Literature')

I got the output

1943
1935
1918
1914

When the tutorial said the answer is

1933
1924
1919
1917

I don't understand why my solution is wrong

EDIT: I saw the careless mistake that 'Literature' should be 'Chemistry' but it still seems to be invalid

Upvotes: 1

Views: 54

Answers (4)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 727047

There are two errors in your query:

  • You mistyped Chemistry (your query says Literature instead)
  • You did not ask for distinct results

Here is your modified query:

SELECT DISTINCT yr
FROM nobel
WHERE subject = 'Physics' AND yr NOT IN
(SELECT yr FROM nobel WHERE subject = 'Chemistry')

DISTINCT is important, because in 1933 the physics prize has been awarded to multiple winners - namely, Dirac and Schrödinger. These two rows from the table result in two entries for 1933 in the output, which you do not want.

Upvotes: 1

Strawberry
Strawberry

Reputation: 33945

SELECT DISTINCT x.yr 
  FROM nobel x
  LEFT
  JOIN nobel y 
    ON y.yr = x.yr
   AND y.subject = 'chemistry'
 WHERE x.subject = 'physics'
   AND y.yr IS NULL;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271023

I want to note that that "Warning" on the question is not correct. You can do this with a group by and having clause:

select n.yr
from nobel n
group by n.yr
having sum(n.subject = 'Physics' ) > 0 and
       sum(n.subject = 'Chemistry' ) = 0;

I am guessing that at this point, the tutorial hasn't introduced group by, having, or using booleans results as integers. However, you do not need a join or subquery to do this.

Upvotes: 1

Andrzej Reduta
Andrzej Reduta

Reputation: 767

In which years was the Physics prize awarded but no Chemistry prize.

:) Read Your task again...

WHERE subject = 'Physics' AND yr NOT IN (SELECT yr FROM nobel WHERE subject = 'Literature')

Upvotes: 1

Related Questions