How do I create a conditional LEFT JOIN?

Question

I am trying to LEFT JOIN 3 tables like so:

DECLARE @CustomerID AS INT;
DECLARE @ProductID AS INT;

SELECT *
FROM table1 t1
    LEFT JOIN table2 t2 ON t1.id = t2.id
    LEFT JOIN table3 t3 ON t2.loc = t3.loc
WHERE t1.id = @ProductID
    AND (t2.loc = t3.loc OR t2.loc IS NULL)
    AND (t3.cid = @CustomerID OR t3.cid IS NULL)

There are 4 basic cases I'm trying to solve for:

1. @CustomerID <> 0 and @ProductID exists in t1 only
2. @CustomerID <> 0 and @ProductID exists in t1 and t2
3. @CustomerID = 0 and @ProductID exists in t1 only
4. @CustomerID = 0 and @ProductID exists in t1 and t2

The code above works for cases 1-3, but returns nothing in case 4. I think it's because the last LEFT JOIN breaks (even though data exists in both t1 and t2 for that @ProductID).

Is there a way to make the second LEFT JOIN conditional without using IF...ELSE logic?

Answer 1

Put the conditions in the on clause instead of the where clause

SELECT *
FROM table1 t1
    LEFT JOIN table2 t2 ON t1.id = t2.id
    LEFT JOIN table3 t3 ON t2.loc = t3.loc
                       AND (t3.cid = @CustomerID OR t3.cid IS NULL)
                       AND (t2.loc = t3.loc OR t2.loc IS NULL)
WHERE t1.id = @ProductID