Combining OUTER JOIN and WHERE

I’m trying to fetch some data from a database. I want to select an employee, and if available, all appointments and other data related to that employee.

This is the query:

SELECT
 TA.id,
 TEI.displayname,
 TA.threatment_id,
 TTS.appointment_date,
        TEI.displayname
    FROM
 tblemployee AS TE
 LEFT OUTER Join tblappointment AS TA ON TE.employeeid = TA.employee_id
 Inner Join tblthreatment AS T ON TA.threatment_id = T.threatmentid
 Inner Join tblappointments AS TTS ON TTS.id = TA.appointments_id AND 
            TTS.appointment_date = '2009-09-28'
        INNER Join tblemployeeinfo AS TEI ON TEI.employeeinfoid = TE.employeeinfoid
        Inner Join tblcustomercard AS TCC ON TCC.customercardid = TTS.customercard_id
    WHERE
     TE.employeeid = 4

The problem is, it just returns null for all fields selected when there are no appointments. What am I not getting here?

Edit: For clearity, i removed some of the collumns. I removed one too many. TEI.displayname should at least be displayed.

Answer

Looking at the list of columns returned by your query, you will notice that they all come from the “right” side of the LEFT OUTER JOIN. You do not include any columns from the “left” side of the join. Therefore, the expected result is the one you are observing — NULL values supplied for all right-hand columns in the result set for those rows that have no right-hand rows returned.

To see data even for those rows, include some columns from TE (tblemployee) in the result set.

Looking at your query I’m guessing that the situation is a bit more complex and that some of those tables on the right-hand side of the join should be moved to the left-hand side and, furthermore, that some of the other tables might possibly require their own OUTER joins to participate correctly in the query.

Edited w/ response to questioner’s comment:

You have an odd situation (maybe not odd at all, depending on your application) in which you have an employee table and a separate employee information (employeeinfo) table.

Because you are joining the employeeinfo to the appointments table with an INNER join you can effectively think of them as a single table in terms of how they contribute to the final result set. Because this combined table REQUIRES a record in the appointments table and because this combined table is joined into the main result set with a LEFT OUTER join, the effect is that the employeeinfo record is not found if there’s no appointment to link it to.

If you move the employeeinfo table to the left side of the join, or replace the employee table w/ the employeeinfo table, you should get the results you want.