Where/having clause help. Not a home work

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

Where/having clause help. Not a home work

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2014-11-20 : 11:57:10

Hi,

I have to over simplified to make my case, but trust me, this is just look-a-like a home work question.

I want to return a list of students whose grades are B or better.

Here is my sample. And notice neither of my Select give me the right answer.

Thanks!

if object_ID('tempdb..#dummySTU') is not null drop table #dummySTU;
if object_ID('tempdb..#dummyGrade') is not null drop table #dummyGrade;

create table #dummySTU (name char(10), ID int)
insert into #dummySTU
Select 'John',11 union
Select 'Jane',22 union
Select 'Jim',33

create table #dummyGrade (ID int, course char(10), grade char(10))
insert into #dummyGrade
Select 11,'Eng','A' union
Select 11,'Hist','B' union
Select 11,'Math','C' union

Select 22,'Eng','A' union
Select 22,'Hist','B' union
Select 22,'Math','A' union

Select 33,'Eng','B' union
Select 33,'Hist','B' union
Select 33,'Math','A'

Select * from #dummySTU

Select * from #dummyGrade

Select a.id, a.name, b.grade from #dummySTU a join #dummyGrade b on a.id = b.id
where b.grade in ('A','B')

Select sum(a.id), sum(a.name), b.grade from #dummySTU a join #dummyGrade b on a.id = b.id
group by b.grade
having b.grade in ('A','B')
--max(), sum() wouldn't work

drop table #dummySTU
drop table #dummyGrade

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-20 : 12:09:28

This query works:


Select a.id, a.name, b.grade from #dummySTU a join #dummyGrade b on a.id = b.id
where b.grade in ('A','B')

What should the output of the next query look like?

Hommer
Aged Yak Warrior

808 Posts

Posted - 2014-11-20 : 13:00:10

returns Jane and Jim (22 and 33) but not John (11) because he has a C in math.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-20 : 13:04:23

That's what the first query does (correctly).

In general, Having should only be used for aggregated columns, since their values is not known before the GROUP BY Clause.

Hommer
Aged Yak Warrior

808 Posts

Posted - 2014-11-20 : 13:18:50

no, that has John in there because he also has A and B.

Will be something like next, but it did get any for now.

select * from #dummySTU
where not exists (
Select a.id from #dummySTU a join #dummyGrade b on a.id = b.id
where b.grade in ('C','D')
)

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-20 : 13:27:31

So you want to exclude students who scored anything other than A or B. This otta do it:


Select a.id, a.name, b.grade from #dummySTU a join #dummyGrade b on a.id = b.id
where b.grade in ('A','B')
  AND NOT exists(
	SELECT 1 FROM #dummyGrade dg
	WHERE a.id = dg.ID
	  AND dg.grade NOT IN ('A', 'B')
	)

Hommer
Aged Yak Warrior

808 Posts

Posted - 2014-11-20 : 14:46:01

Great, thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources