HELP with sql hw. A subquery...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

HELP with sql hw. A subquery...

Author

Topic

stuckne1
Starting Member

22 Posts

Posted - 2010-09-29 : 21:47:14

3a. In which years was the Physics prize awarded but no Chemistry prize. (WARNING - this question is way too hard for this level, you will need to use sub queries or joins).

This is what I put, but it displays all subjects. I don't understand why it is doing this.

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

Sachin.Nand

2937 Posts

Posted - 2010-09-29 : 22:32:45

[code]
select yr from yourtable group by yr
having count(case when subject = 'Physics' and subject != 'Chemistry' then 1 else 0 end)=1
[/code]

PBUH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-30 : 12:01:51

it should be


select yr from yourtable group by yr
having count(case when subject = 'Physics' then 1 else 0 end)=1
and count(case when subject = 'Chemistry' then 1 else 0 end)=0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-30 : 12:10:54

PAH -- sub queries or JOINs be damned! There is probably a vanilla SQL answer using EXCEPT (performance will probably be bad though)


DECLARE @nobel TABLE (
	[yr] INT
	, [Subject] VARCHAR(255)
	)

INSERT @nobel
      SELECT 1950, 'Physics'
UNION SELECT 1950, 'Chemistry'
UNION SELECT 1951, 'Physics'
UNION SELECT 1952, 'Chemistry'
UNION SELECT 1952, 'Physics'
UNION SELECT 1953, 'Biology'
UNION SELECT 1954, 'Physics'

SELECT yr
FROM @nobel
WHERE [subject]='Physics'

EXCEPT SELECT yr
FROM @nobel
WHERE [subject]='Chemistry'

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-09-30 : 12:16:15

Another subquery version:

SELECT yr FROM nobel N
WHERE subject = 'Physics' 
AND NOT EXISTS (SELECT * FROM nobel WHERE yr=N.yr AND subject = 'Chemistry')

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-30 : 12:27:10

quote:
Originally posted by stuckne1

3a. In which years was the Physics prize awarded but no Chemistry prize. (WARNING - this question is way too hard for this level, you will need to use sub queries or joins).

This is what I put, but it displays all subjects. I don't understand why it is doing this.

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

You are almost there actually. With a slight modification to



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

You get the right results.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources