SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Hybrid View

  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2009
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2009
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you so much - you are an absolute genius! - it works a treat.

    Thank you, thank you, thank you.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •