Re: MySQL sql query help

[Bruce Feist]

Ben Balbo wrote:

Teacher (id, name, ...)
TeacherClass (teacherid, classid, timestamp)
Class (id, name, ...)
Now, what I'd really like to do it find all teachers who, for example,
taught Engligh and Math, but not Biology, 

Here's a general, recursive, untested solution for you to play with.

We start with a query that returns a list of all teachers.  Don't worry
about the DISTINCT or WHERE clause; it's used to extend the idea to the
recursion.
SELECT  distinct t.name
FROM Teachers t
WHERE 1 = 1
;
If you want to AND in a condition on teaching a class, for instance
English, put parentheses around the old joins and (substituting an
appropriate counter for '2') add:
INNER JOIN (TeacherClass tc2 INNER JOIN Class c2 ON tc2.classid =
c2.classid AND c2.name = 'English') ON t.teacherid = tc2.teacherid
If you want to AND in a condition on NOT teaching a class, for instance
Biology, put parentheses around the old joins and (substituting an
appropriate counter for '3') add:
LEFT JOIN (TeacherClass tc3 INNER JOIN Class c3 ON tc3.classid =
c3.classid AND c3.name = 'Biology') ON t.teacherid = tc3.teacherid
and add the following to the WHERE clause:

AND tc3.classid IS NULL

Give it a try and let me know if it works!

Bruce Feist



---------------------------------------------------------------------
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/           (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php