CODEWITHSUNDEEP
sqlt-sql
MonuMan5
MonuMan5

Reputation: 373

Noob Concern: T-SQL Nesting Subquery

I've got a table named nobel with the following fields: yr, subject and winner.

This table represents winners of nobel prize winners in a variety of subjects over the years.

I am looking for the years in which the Physics Prize was awarded but no Chemistry prize.

I was thinking the answer would look something like the following: 

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

I'm fairly certain I've got the syntax wrong... and i'm not certain i'm going down the right road. Help would be appreciated. Thanks!

Upvotes: 1

Views: 95

Answers (5)

Pratik
Pratik

Reputation: 85

Below code will give you required results.

 SELECT n1.yr
    FROM nobel n1
    WHERE subject = 'physics'
        AND NOT EXISTS (
            SELECT yr
            FROM nobel n2
            WHERE subject = 'chemistry' AND n2.yr = n1.yr
            )

Upvotes: 1

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

Unless you're using SQL Server 2000, EXCEPT works on 2005+.

    SELECT yr
      FROM nobel 
     WHERE subject = 'Chemistry'
EXCEPT
    SELECT yr
      FROM nobel
     WHERE subject = 'Physics'

Upvotes: 1

AbdElRaheim
AbdElRaheim

Reputation: 1394

We can do two derived tables and join them.

SELECT c.Year FROM 
c (SELECT Year, COUNT(*) FROM nobel where subject = 'chem' GROUP By Year) INNER JOIN
p (SELECT Year, COUNT(*) FROM nobel where subject = 'phy' GROUP By Year) ON c.Year = p.Year
WHERE c.Count != 0 and p.Count = 0

Upvotes: 0

Stuart Ainsworth
Stuart Ainsworth

Reputation: 12940

Try this:

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

Upvotes: 3

Greg
Greg

Reputation: 3522

I think you might be heading down the wrong path. The where clause you have is essentially saying where 'Chemistry' = 'Physics' which will never be true.

What I think you need to do is to find out what years physics and chemistry were awarded, THEN find the years that only physics was awarded.

SELECT yr
FROM Nobel
LEFT JOIN
(
  SELECT SUM(1), yr
  FROM  Nobel
  WHERE subject = 'Chemistry'
  Group By yr
) as Chemistry
ON Chemistry.yr = Nobel.Yr
LEFT JOIN
(
  SELECT SUM(1), yr
  FROM  Nobel
  WHERE subject = 'Physics'
  Group By yr
) as Physics
ON Physics.yr = Nobel.yr

WHERE Physics.YR IS NOT NULL AND Chemistry.Yr IS NULL

GROUP BY yr

Upvotes: 0

Related Questions