I don't know if it would be possible to use something like this, but maybe it's worth a try...
SELECT Customers.*, SUM(IF(IFNULL(Orders_Items.productid, 0)='23',1,0)) BINGO FROM Customers LEFT JOIN Orders ON (Orders.customerid=Customer.CustomerID) LEFT JOIN Ordered_Items ON (Ordered_Items.orderid=Orders.id && (Ordered_Items.productid = '23')) WHERE Customers.id=Subscriptions.customerid AND Subscriptions.listid='2' AND Ordered_Items.orderid IS NULL AND Orders.customerid IS NULL AND NOT Customers.bad_email GROUP BY Customer.email HAVING BINGO = 0; Jeez.... either I should be getting some coffee or some sleep.... Peter Normann -----Original Message----- From: Jamie Tibbetts [mailto:[EMAIL PROTECTED]] Sent: 18. juni 2002 22:21 To: Peter Normann; [EMAIL PROTECTED] Subject: Re: Need help with a complex query >>> Try - and I mean *try* :-) >>> >>> SELECT Customers.* FROM Customers, Subscriptions >>> LEFT JOIN Orders ON (Orders.customerid=Customer.CustomerID) >>> LEFT JOIN Ordered_Items ON (Ordered_Items.orderid=Orders.id && >>> (Ordered_Items.productid = '23')) >>> WHERE Customers.id=Subscriptions.customerid >>> AND Subscriptions.listid='2' >>> AND Ordered_Items.orderid IS NULL >>> AND Orders.customerid IS NULL >>> AND NOT Customers.bad_email; >> >> Well, MySQL has been chugging on this one for about 15 minutes now, and >> it's >> still going. So I'm not sure if it is a problem with the query or the >> optimization of my MySQL databases. There are 30,000+ records in the >> Customers database, but the query should have finished by now, right? >> > Well, my best guess is that you should consider indexing your tables... > If you haven't done so already, indexing will cause dramatic effect on > the speed of the queries... > > Peter Normann Okay. I've indexed all fields involved, and I've gotten the query time down to under 3 seconds! However, I've discovered a flaw in the query. A customer can order multiple times. The above query still includes customers that have ordered product #23, but have also ordered other products on separate orders. Example: Customers: Subscriptions: +------+-----------+ +------------+--------+ | id | bad_email | | customerid | listid | +------+-----------+ +------------+--------+ | 1001 | 0 | | 1001 | 2 | +------+-----------+ +------------+--------+ Orders: Ordered_Items: +----+------------+ +---------+-----------+ | id | customerid | | orderid | productid | +----+------------+ +---------+-----------+ | 1 | 1001 | | 1 | 23 | | 2 | 1001 | | 2 | 98 | +----+------------+ +---------+-----------+ Customer #1001 would be included in the found set of the query above. He would be removed because he ordered product #23 on order #1, but he would also be included because he didn't order product #23 on order #2. Any ideas on how to exclude these multi-order customers? Jamie Jamie Tibbetts ..... [EMAIL PROTECTED] Epigroove ..... http://www.epigroove.com/ --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.371 / Virus Database: 206 - Release Date: 13-06-2002 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.371 / Virus Database: 206 - Release Date: 13-06-2002 --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
