Nookster Posted March 9, 2012 Share Posted March 9, 2012 Hi guys, I've been trying to figure this one out for about 2 hours now, how can I issue a MySQL query to the database that will show me customers who have an account but have not made an order? Thanks a lot in advance, Steve Link to comment Share on other sites More sharing options...
burt Posted March 9, 2012 Share Posted March 9, 2012 SELECT customers_id, customers_firstname, customers_lastname, customers_email_address FROM customers WHERE customers_id NOT IN ( SELECT customers_id FROM orders ) Explanation: Select the information you need from the customers table (remember that all customers here have an account), THEN only show those details IF the customers_id is not found in the customers_id column of the orders table. Link to comment Share on other sites More sharing options...
Nookster Posted March 9, 2012 Author Share Posted March 9, 2012 SELECT customers_id, customers_firstname, customers_lastname, customers_email_address FROM customers WHERE customers_id NOT IN ( SELECT customers_id FROM orders ) Explanation: Select the information you need from the customers table (remember that all customers here have an account), THEN only show those details IF the customers_id is not found in the customers_id column of the orders table. Thanks a lot! And thank you for the explanation :)! Link to comment Share on other sites More sharing options...
Mort-lemur Posted March 9, 2012 Share Posted March 9, 2012 This has got me thinking, would it be possible to modify this to delete the customers who have never purchased, say after a set period of time to keep the customer database up to date? Now running on a fully modded, Mobile Friendly 2.3.4 Store with the Excellent MTS installed - See my profile for the mods installed ..... So much thanks for all the help given along the way by forum members. Link to comment Share on other sites More sharing options...
burt Posted March 9, 2012 Share Posted March 9, 2012 It would be a lot more lines of code as you would need to do some joins on address_book, customers_info. Then apply it to orders newer than X date. The answer is though; "yes". Link to comment Share on other sites More sharing options...
Nookster Posted March 10, 2012 Author Share Posted March 10, 2012 This is why I was asking, I was going to try to build a contribution that will let you do this, but I am a complete amateur, got to learn sometime though right? I figured out that I would have to do joins but couldn't really get my head around it when I tried. How would I also get it to return the date that the user joined to return with each first name last name and email address, Perhaps I'm not the best person to be trying to do this @_@! Link to comment Share on other sites More sharing options...
MrPhil Posted March 11, 2012 Share Posted March 11, 2012 I wouldn't immediately give the boot to purchase-less customers. First, I'd send them an email saying that we've noticed that you signed up as a customer, but haven't purchased anything. Is there something preventing you from making a purchase? Have you had any problems with using the site, that we could help you with? Are shipping terms or costs a problem [consider offering lower cost shipping options, such as Parcel Post]? Are you unable to make use of any of our payment options [offer COD, check, money order]? Etc., etc. If a few of these convert into paying customers, you'll come out ahead. Plus, their email addresses (if current) can receive newsletters and mailings (provided they agreed to that when they signed up, so you wouldn't be violating anti-spam laws). If they don't purchase anything even after this, then pull the plug on them. Link to comment Share on other sites More sharing options...
♥toyicebear Posted March 11, 2012 Share Posted March 11, 2012 Click me>> Basics for osC 2.2 Design - Basics for Design V2.3+ - Seo & Sef Url's - Meta Tags for Your osC Shop - Steps to prevent Fraud... - MS3 and Team News... - SEO, Meta Tags, SEF Urls and osCommerce - Commercial Support Inquiries - OSC 2.3+ How To To see what more i can do for you check out my profile [click here] Link to comment Share on other sites More sharing options...
burt Posted March 13, 2012 Share Posted March 13, 2012 SELECT c.customers_id, c.customers_firstname, c.customers_lastname, c.customers_email_address, ci.customers_info_date_account_created, ci.customers_info_number_of_logons, ci.customers_info_date_of_last_logon FROM customers c LEFT JOIN customers_info ci ON c.customers_id = ci.customers_info_id WHERE c.customers_id NOT IN ( SELECT customers_id FROM orders WHERE date_purchased > DATE_SUB(NOW(), INTERVAL 1 YEAR) ) ORDER BY customers_id That might do it (phpmyadmin). You can change the interval to any number and any of HOUR DAY WEEK MONTH YEAR. NOTE: I have not tested this! Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.