I'm building a shopping site and would like to email customers who've registered but haven't bought anything from the site in the last 6 months.
I have two tables:
tblcustomers is the table where the registered customers are stored, the key fields are:
custId (unique id)
custName (their name)
custEmail (their email address)
I then have another table for orders placed on the website: tblshoporders, the key fields are:
OrderId (unique id)
OrderCustId (the id of the customer who placed the order)
OrderDate (the date of the order (YYYY-MM-DD)
I need to create a query that will make a recordset of customers who have not bought anything in the last six months. If they have made a purchase (within the last 6 months) they can be ignored.
I've been trying various queries but the problem I'm getting is that a single customer may have placed several orders over different dates but I only need to make sure they haven't bought anything in the last six months - also my query is finding only customers that "have" made a purchase, I also want any customer that hasn't made a single purchase yet (i.e. is not in the tblshoporders table).
Any help would be gratefully received.
the way to do this is the classic "unmatched" query, a.k.a. LEFT OUTER JOIN with IS NULL test
strategy: write the left join to look for the matches you ~don't~ want to find
this means all the necessary condition go into the ON clause -- in this case, the date condition
then add a WHERE clause to restrict the results to unmatched rows, i.e. where a NULL is returned in the main join column
FROM tblcustomers AS c
JOIN tblshoporders AS o
ON o.OrderCustId = c.custId
AND o.OrderDate >= CURRENT_DATE - INTERVAL 6 MONTH
WHERE o.OrderCustId IS NULL
Thank you so much - you are an absolute genius! - it works a treat.
Thank you, thank you, thank you.
This topic is now closed. New replies are no longer allowed.