CODEWITHSUNDEEP
sql-server
badghost
badghost

Reputation: 73

How do I create a conditional LEFT JOIN?

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?

Upvotes: 7

Views: 16631

Answers (2)

Charles Bretana
Charles Bretana

Reputation: 146499

If I understand what you want, this might work:

SELECT * FROM table1 a
    LEFT JOIN table2 b 
      ON b.id = a.id
    LEFT JOIN table3 c 
      ON c.loc = b.loc
         and isNull(c.cid, @CustomerID) = CustomerID 
WHERE t1.id = @ProductID

Upvotes: 0

juergen d
juergen d

Reputation: 204766

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

Upvotes: 14

Related Questions