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?
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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')
)
Go to Top of Page

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')
)
Go to Top of Page

Hommer
Aged Yak Warrior

808 Posts

Posted - 2014-11-20 : 14:46:01
Great, thanks!
Go to Top of Page
   

- Advertisement -