Extra condition in ON clause is ignored

Question

I have this query:

 SELECT
  TA.id,
  T.duration,   
  DATE_FORMAT(TA.startTime,'%H:%i') AS startTime,
  TEI.displayname,
  TA.threatment_id,
  TE.employeeid,
  TTS.appointment_date
 FROM
  tblEmployee AS TE
  INNER Join tblEmployeeInfo AS TEI ON TEI.employeeinfoid = TE.employeeinfoid
  LEFT OUTER Join tblAppointment AS TA ON TE.employeeid = TA.employee_id
  LEFT OUTER Join tblThreatment AS T ON TA.threatment_id = T.threatmentid
  LEFT OUTER Join tblAppointments AS TTS ON TTS.id = TA.appointments_id 
   AND TTS.appointment_date = '2009-10-19'
  LEFT OUTER Join tblCustomerCard AS TCC ON TCC.customercardid = TTS.customercard_id
 WHERE
  TE.employeeid = 1

What I try to accomplish is to select an employee, if available, all appointments at a given date. When there aren't any appointments, it should at least give the information about the employee.

But right now, it just gives all appointments related to an employee, and passes null when the date doesn't match. Whats going wrong here?

Answer 1

Because you are doing a left OUTER join, it will only join those records that match the On condition and will attach Null when the condition is not met.

You will still get records for which there is no Appointments on that date.

If you did an INNER join, then if the On condition is not met, no record will be output. So you will not get any records for which there are no appointments on that date.

Answer 2

Ok, not sure what database you are on, but this would work on SQL server :

select * from tblEmployee TA
...
left join 
( select * from tblAppointments ed where ed.appointment_date = '10/01/2008' ) TTS
on  ON TTS.id = TA.appointments_id

Thats the vibe anyway! You might need to tinker a bit.. Im at work and cant get the whole thing going for ya! :)