Select help

[Bell1991]

I have a table that has:

Name Grade
John A
John B
John C
Eric C
Seth B
Seth A
Tim A

I want to retun only Name that have an 'A' but no B or C
so in the above only Tim would be returned..

I tried
Select * from tblTable
where Grade = 'A' and Grade not in ('B', 'C') and i am still not getting the results back needed.

 

[macleod1021]

drop the "and Grade not in('B','C')" and it will work.

 

[bborissov]

SELECT Name, MAX(Grade) AS Grade
FROM tblTable
GROUP BY Name
HAVING MAX(Grade) = 'A'

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[Bell1991]

but that will still return records where the grade is a b or c.

If a student has a A i want that record.
If the student has an a and b - i do not want that record
if the student has an a and c - i do not want that record
if the student has a and b and c - i do not want that record

 

[Crowley16]

select name from table
where grade = 'a' and name not in (
select distinct name from table where grade in ('b','c')
)

although I'm not sure how effecient this will be...

--------------------
Procrastinate Now!

 

[bborissov]

Nope. Try it.
MAX('A') is smaller than MAX('B')

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP