It wouldn't work because with a join like that, you'll be getting procedures from other doctors (in fs) matching up with procedures in p. So if *any* doctor had a procedure defined in fs, it'd get matched up, regardless of whether the doctor you're working with has it set up.
What's needed is a way to get rid of all records in fs where fs.doctor_id != $doctor_id *before* the join occurs. Dan > Wouldn't this work? > > select description from procedures p left join fee_schedules fs > ~ on fs.procedure_id=p.id and fs.doctor_id=2 > ~ where fs.id is null; > > ~From what I understand, a LEFT JOIN will create the result set as if it > were an INNER JOIN with the LEFT JOIN's ON clause as the WHERE clause, > then add a row (supplemented with NULLs for the columns from the right > hand side) for each record in the left hand side not yet appearing. > > Limiting the doctor_id in the ON clause of the LEFT JOIN should have the > same effect then as reducing the original table to a temporary table > using a simple select. > > Am I wrong? > > Jacob Fugal ____________________ BYU Unix Users Group http://uug.byu.edu/ ___________________________________________________________________ List Info: http://uug.byu.edu/cgi-bin/mailman/listinfo/uug-list
