Hi Bruce,
Thanks for your reply. I just got the time to take a look at it, and
it makes sense. However, for the first test:
SELECT distinct t.name
FROM Teacher t
INNER JOIN (TeacherClass tc2 INNER JOIN Class c2 ON tc2.classid =
c2.id AND c2.name = 'English') ON t.teacherid = tc2.teacherid
;
I'm getting:
ERROR 1064: You have an error in your SQL syntax near
'(TeacherClass tc2 INNER JOIN Class c2 ON tc2. classid = c2.id AND
c2.name = 'Eng' at line 3;
Seems MySQL doesn't much like having nested joins in joins...
However, your principles led me to my result. As the search page will
pass the class ids in anyway, I don't need to read these from the
table. I.E. I pass the value 1 to the search script, not "Math".
Here's the working version in psuedo code:
$sqlquery = "select distinct t.name from teacher t ";
for each required subject
$sqlquery .= "inner join teacherclass tc$counter ".
"on t.id=tc$counter.teacherid ".
"and tc$counter.classid=$subjectid ";
$counter++;
}
$whereclause="";
for each excluded subject
$sqlquery .= "left join teacherclass tc$counter ".
"on t.id=tc$counter.teacherid ".
"and tc$counter.classid=$subjectid ";
$whereclause .= ($whereclause=="") ? "where " : "and ";
$whereclause .= "tc$counter.classid is null ";
$counter++;
}
So a search with has taught 1, 3 & 6, and has not taught others:
select distinct t.name from teacher t
inner join teacherclass tc1 on t.id=tc1.teacherid and tc1.classid=1
left join teacherclass tc2 on t.id=tc2.teacherid and tc2.classid=2
left join teacherclass tc3 on t.id=tc3.teacherid and tc3.classid=3
inner join teacherclass tc4 on t.id=tc4.teacherid and tc4.classid=4
left join teacherclass tc5 on t.id=tc5.teacherid and tc5.classid=5
inner join teacherclass tc6 on t.id=tc6.teacherid and tc6.classid=6
where tc2.classid is null and tc3.classid is null and tc5.classid is null
;
Remove any joins if you don't care either way if the teacher taught
that subject. Switch inner to left and vice versa to change condition,
and each left join should have a where is null clause.
Thanks again Bruce, my favourite type of help, a pointer but allow me
to learn why it works :-)
I'm sure you knew most of the above, but I thought I'd included it all
for the benefit of others on the list/anyone searching archives who
may need this.
Best of all, I think I'm a little closer to understanding joins!
Rgds,
Ben Balbo
In response to your mail sent on Saturday, March 08, 2003 at 2:18:34 AM.
> 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
---------------------------------------------------------------------
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
