MySQL query for creating a recordset of users who've been inactive for 6 months

[scim1971]

Hi,

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.

Many thanks.

[r937]

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

SELECT c.custId
     , c.custName
     , c.custEmail
  FROM tblcustomers AS c
LEFT OUTER
  JOIN tblshoporders AS o
    ON o.OrderCustId = c.custId
   AND o.OrderDate >= CURRENT_DATE - INTERVAL 6 MONTH
 WHERE o.OrderCustId IS NULL

[scim1971]

Thank you so much - you are an absolute genius! - it works a treat.

Thank you, thank you, thank you.