Query multiple columns and rows

[cpgoose]

Hi everyone, great site. I've been looking around for a while, and now this is my first post. I'm having a SQL problem...any help would be great. I have a table (SCHEDULE_t) that contains teachers and the classes they teach. See below:

TEACHER CLASS
0001 MATH
0001 HISTORY
0002 HEALTH
0002 CHEMISTRY
0003 MATH
0003 HISTORY
0004 MATH
0004 GYM

Basically, I'm trying to find the teachers that teach MATH, but not GYM. In other words, 0001 and 0003 would show up, but not 0004 (he teaches MATH, but since he also teaches GYM he won't be on the list). I understand how to find the teachers that teach MATH, or the teachers that don't teach GYM, but I can't figure out how to do both.

Arg, any hints would be great!

 

[dhookom]

Try this sql:

SELECT TEACHER
FROM SCHEDULE_T
WHERE CLASS "MATH"
AND TEACHER NOT IN
(SELECT TEACHER FROM SCHEDULE_T WHERE CLASS = "GYM")

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[steven290]

try this

SELECT teacher from SCHEDULE_t
where class='math' and class<>'gym' and teacher<>(select teacher from SCHEDULE_t where class='gym')

 

[cpgoose]

Thanks guys, that was a great help! The first one from dhookom I got to work, but the one from steven290 keeps giving me an error that says "At most one record can be returned at this subquery".

Anywho, for the first one that I got to work, I don't quite understand why that works. Does it do the subquery first? So, it finds all of the teachers that do not teach GYM, and then it finds the teachers in that list that equal MATH?

 

[traingamer]

It does the subquery first - it finds all of the teachers who teach gym. Then it finds all of the teachers who teach math who are NOT IN that list 0f gym teachers.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[lespaul]

that's because steven's SQL is returning multiple teachers who teach gym, but the comparison operator <> only works for a single value. You have to use NOT IN or IN if a subquery is going to return multiple values.

Leslie