Hi all, I've been trying to wrap my head round this problem for a few days now, and have plenty of pieces of scrap paper with lines and arrows and numbers to prove it.
Anyway, here's the scenario: I have a table of teachers, and a table of classes. Any teacher can have taught any class, and any class can be taught by any teacher. Each lesson taken has a timestamp (so a teacher can teach a class more than once). Teacher (id, name, ...) TeacherClass (teacherid, classid, timestamp) Class (id, name, ...) Teachers: (1, "Bob") (2, "Kate") (3, "Mike") Classes (1, "Math") (2, "English") (3, "Biology") (4, "Chemistry") (5, "Physics") (6, "Sport") Easy enough. Okay, so Bob has taught Math, Kate taught Math and Biology, and Mike taught everything bar Biology. I won't bother showing the TacherClass table contents. So, now I want to do a search on certain circumstances. I can search for teachers who taught Math, that's easy. I can even search for those who didn't teach Engligh: select t.name from teacher t left join teacherclass tc on t.id=tc.techerid and tc.classid=2 where tc.teacherid is null; Now, what I'd really like to do it find all teachers who, for example, taught Engligh and Math, but not Biology, and I don't care either way about the other classes. This would return "Mike", but the actual SQL is baffling me. If I understand correctly, I'm going to have to join (probably left) the teacherclass table x-1 times where x is the number of constraints. So in my example above, I'll need 2 joins to get a column for class=2, class=1 and class!=3. Has any one got any pointers, hints, advice, solutions, links to online resources, etc, that could help me? Many thanks in advance... Ben --------------------------------------------------------------------- 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
