Help! I'm trying to ID our innactive customers.

[mOrloff]

Here's what i've got so far.
It returns zero rows with no errors.
SELECT companyID
FROM Company
WHERE NOT EXISTS (
SELECT companyID
FROM SalesOrder
WHERE date > '2009-07-01')

I am trying to find which customers have not purchased since that date.

Please show me the error of my ways.

~ Mo

[Fumigator]

You have to tie the two tables together somehow. Right now your subquery is selecting EVERYTHING in the database with a date greater than 2009-07-01.

For example:

SELECT companyID
FROM Company
WHERE NOT EXISTS (
SELECT companyID
FROM SalesOrder
WHERE date > '2009-07-01'
AND SalesOrder.companyID = Company.companyID)

[bazz]

Is there a login record to see how active they are? I mean if they haven't logged in they are inactive. If they have logged in~ but not bought ~ they are active but haven't bought anything.

key differences:
1. they want to buy but aren't - an issue for the shop because something is actually dissuading them from buying.

2. they don't want to buy and so, aren't even looking. potentially permanently lost customers.

would it be better to identify which group they fall into so you can catch group 1 before they fall into the much more sticky; group 2? If so, you need to check logins as well as purchases rather than only the purchases.

my 2c

bazz

[Old Pedant]

I think a better/faster version of that query would be:

SELECT DISTINCT C.companyID, C.companyName
FROM Company AS C LEFT JOIN SalesOrder AS S
ON C.companyID = S.companyID
WHERE IFNULL(S.`date`,'2000-01-01') < '2009-07-01'

or maybe even

SELECT companyID
FROM Company
WHERE companyID NOT IN (
SELECT companyID FROM SalesOrder
WHERE `date` > '2009-07-01'
)

I'm not sure about the efficiency of the NOT EXISTS with the hidden join there.


And since date is a builtin function in MySQL and hence a keyword, you really should play it safe and use the `...` backticks around the field name.

[mOrloff]

OldPendant and Fumigator, thanks a bunch. Those were very helpful. (Man, I always forget about NOT IN!)

Bazz, I am looking at an internal DB which the customers don't actually access at all.
It is a DB into which salespeople manually(ish) enter orders directly.

~ Mo