Combine Customer and Purchase Date tables for latest purchase, but include nulls

I’ve got two tables, one where customer ID is store and another that stores each date they had a purchase on. I am stuck on keeping all new customers that don’t have a purchase date yet when querying for the max purchase date for all customers.

CustomerTable:

PurchaseDates:

I have set up a view in SQL that combines the two and queries for the MAX purchase date for each customer. The problem is that since I am using MAX, customers that have not purchased anything yet do not show up as they either do not have an entry in PurchaseDates table or their purchase_date field is blank.

My SQL View Code:

SELECT ct.CustomerID,
       ct.Full_Name,
       pd.Purchase_Date,

FROM   CustomerTable AS ct
       LEFT OUTER JOIN PurchaseDates AS pd
                    ON ct.CustomerID = pd.CustomerID
WHERE  EXISTS (SELECT 1 
               FROM   PurchaseDates  AS pd_latest
               WHERE  ( CustomerID= pd.CustomerID)
               GROUP  BY CustomerID
               HAVING ( Max(Purchase_Date) = pd.Purchase_Date)) 

The result in my example above yields only customerID 1 with the purchase date of 11/21/2021, but I’d like to also display CustomerID 2 with a null date for their purchase_date. Not really sure how to proceed apart from seeing that some have opted to replace all nulls with arbitrary days.

The end result should be

Appreciate the help

>Solution :

You only need a single value from the PurchaseDates table so a simple correlated subquery is all you require:

select ct.CustomerID, ct.Full_Name, 
    (
        select Max(pd.Purchase_Date)
        from PurchaseDates pd
        where pd.CustomerId = ct.CustomerId
    ) as Purchase_Date
from CustomerTable ct;

Should more than a single column be required then you could apply the appropriate row:

select ct.CustomerID, ct.Full_Name, pd.*
from CustomerTable ct
outer apply (
    select top(1) *
    from PurchaseDates pd
    where pd.CustomerId = ct.CustomerId
    order by pd.Purchase_date desc
)pd;