Combining OUTER JOIN and WHERE

Question

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 1

A visual explanation of SQL join

Answer 2

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.

Answer 3

The query is performing as it should.

A left out join will select all records from one table, join them with the records in another, and produce nulls where no records in the second table are found that match the join condition.

If you're looking for a separate behavior, you may want to think about two separate queries.

Answer 4

The issue is that the way you're joining (most of everything is joining to your left outer-joined table) whenever you're joining off of that, if the value in the outer joined table is nothing, there is nothing for the other fields to join to. Try to re-adjust your query so everything is joining off of your employeeID. I normally use left joined tables after I've limited everything down as much as possible with inner joins.

So my query would be something like:

SELECT TA.id, TEI.displayname, TA.threatment_id, TTS.appointment_date FROM tblemployee AS TE INNER Join tblemployeeinfo AS TEI ON TEI.employeeinfoid = TE.employeeinfoid 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 tblcustomercard AS TCC ON TCC.customercardid = TTS.customercard_id LEFT OUTER Join tblappointment AS TA ON TE.employeeid = TA.employee_id WHERE TE.employeeid = 4

where the last outer join just gives me one column worth of information, not using it all to join more things onto. For speed, you also want to try to limit your information down as fast as possible with your first few inner joins, and then you do the outer joins last to join possible null values on to the smallest dataset you can. I hope this helps, if it's confusing, I'm sorry... I haven't had my caffeine yet.

Answer 5

In your query, you LEFT OUTER JOIN to the tblappointment table, but then you INNER JOIN to the tblthreatment and tblappointments tables.

You should try and structure your query in the order that you expect data to be there. Then in most simple queries, once you perform an OUTER join, most tables after that will be an OUTER join. This is by NO MEANS a rule and complex queries can vary, but in the marjority of simple queries its a good practice.

Try something like this for your query.

SELECT
 TA.id,
 TEI.displayname,
 TA.threatment_id,
 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-09-28'
LEFT OUTER JOIN
 tblcustomercard AS TCC
ON
 TCC.customercardid = TTS.customercard_id
WHERE
 TE.employeeid = 4