CODEWITHSUNDEEP
sqlsql-servert-sqlleft-join
user1501171
user1501171

Reputation: 230

Joining onto NULL if Record does not exist SQL

I have a query that selects customers from a table CustomerDetails, and left joins onto another table (CustomerActivity) to get their last login time and finally left joins onto another table (OpenOrderDetails) to get their last open order (if applicable). I also have a big WHERE clause filtering this data

A customer can only have one record in the OpenOrderDetails table at anytime. My query looks like the following: 

SELECT CD.*, H.LastCustomerLoginTime, OD.OrderFiledDate, OD.OrderCompletedDate
    FROM CustomerDetails CD
    LEFT JOIN CustomerActivity H ON H.CustomerID = CD.CustomerID 
    LEFT JOIN OpenOrderDetails OD ON CD.CustomerID = OD.CustomerID
WHERE CD.OrderStatus IN (1,2,3)
    AND (CustomerType = 1 or (CustomerType = 3 and CustomerActive IN (1,2)))
    AND (OD.OrderFiledDate IS NULL OR CD.TimeStamp >= OD.OrderFiledDate)
    AND (OD.OrderCompletedDate IS NULL OR CD.TimeStamp <= OD.OrderCompletedDate)

My issue is that this query only returns customer records that have a record in the OpenOrderDetails table. How do I return every customer, and OrderFiledDate/OrderCompletedDate if present, and NULL if a record for that customer does not exist in the OpenOrderDetails table?

Upvotes: 3

Views: 8149

Answers (2)

Jason A. Long
Jason A. Long

Reputation: 4442

When doing a LEFT JOIN, referencing ANY of the right side table columns in the WHERE clause will turn it into an INNER JOIN. To get around this, simply remove any and all predicates that reference "right side" columns from the WHERE clause and move them to the LEFT JOIN condition.

Something along these lines...

SELECT
    CD.*, 
    H.LastCustomerLoginTime, 
    OD.OrderFiledDate, 
    OD.OrderCompletedDate
FROM
    CustomerDetails CD
    LEFT JOIN CustomerActivity H
        ON H.CustomerID = CD.CustomerID
    LEFT JOIN OpenOrderDetails OD
        ON CD.CustomerID = DLECS.CustomerID
        AND (   OD.OrderFiledDate IS NULL
            OR  CD.TimeStamp >= OD.OrderFiledDate
        )
        AND (   OD.OrderCompletedDate IS NULL
                OR  CD.TimeStamp <= OD.OrderCompletedDate
            )
WHERE
    CD.OrderStatus IN ( 1, 2, 3 )
    AND (
            CustomerType = 1
            OR  
            (   
                CustomerType = 3
                AND CustomerActive IN ( 1, 2 )
            )
        );

Upvotes: 5

TriV
TriV

Reputation: 5148

Just move some OpenOrderDetails conditions to join clause

 SELECT CD.*, H.LastCustomerLoginTime, OD.OrderFiledDate, OD.OrderCompletedDate
FROM CustomerDetails CD
LEFT JOIN CustomerActivity H ON H.CustomerID = CD.CustomerID 
LEFT JOIN OpenOrderDetails OD   ON CD.CustomerID = DLECS.CustomerID
                    AND (OD.OrderFiledDate IS NULL OR CD.TimeStamp >= OD.OrderFiledDate)
                    AND (OD.OrderCompletedDate IS NULL OR CD.TimeStamp <= OD.OrderCompletedDate)
WHERE CD.OrderStatus IN (1,2,3)
AND (CustomerType = 1 or (CustomerType = 3 and CustomerActive IN (1,2)))

Upvotes: 1

Related Questions