Extra condition in ON clause is ignored

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 A开发者_开发知识库S 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?

---

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.

---

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! :)