scim1971 — 2013-02-27T09:32:59-05:00 — #1
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.
r937 — 2013-02-27T10:32:12-05:00 — #2
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
scim1971 — 2013-02-27T10:41:11-05:00 — #3
Thank you so much - you are an absolute genius! - it works a treat.
Thank you, thank you, thank you.