user1501171 user1501171 - 21 days ago 8
SQL Question

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 = DLECS.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?

Answer Source

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)))