RE: Need help with a complex query
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
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/ - 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
RE: Need help with a complex query
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 -Original Message- From: Jamie Tibbetts [mailto:[EMAIL PROTECTED]] Sent: 16. juni 2002 19:18 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? Jamie Jamie Tibbetts.[EMAIL PROTECTED] Epigroove .http://www.epigroove.com/ - 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 - 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
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? Jamie Jamie Tibbetts.[EMAIL PROTECTED] Epigroove .http://www.epigroove.com/ - 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
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; Peter Normann -Original Message- From: Jamie Tibbetts [mailto:[EMAIL PROTECTED]] Sent: 16. juni 2002 18:23 To: [EMAIL PROTECTED] Subject: Need help with a complex query I am currently a web designer who's taught himself the necessary basics of PHP and MySQL. I've reached a point where I can't figure out a complex query. I have a mailing list that customers can sign up for if they want to receive "Special Offer" mailings when products go on sale. So when it's time to send out a mailing on a sale product, I need to select all customers who have signed up for list #2. However, (and here's the big problem) I need to filter out the customers that have bad email addresses marked in their customer record, *and* I need to filter out the customers who have already purchased the sale product. Customers: Subscriptions: +--+---+ +++ | id | bad_email | | customerid | listid | +--+---+ +++ | 1001 | 1 | | 1001 | 2 | | 1002 | 0 | | 1002 | 1 | | 1003 | 0 | | 1003 | 2 | | 1004 | 0 | | 1004 | 2 | | 1005 | 0 | | 1005 | 2 | +--+---+ +++ Orders: Ordered_Items: +++ +-+---+ | id | customerid | | orderid | productid | +++ +-+---+ | 1 | 1001 | | 1 | 24| | 2 | 1002 | | 2 | 98| | 3 | 1003 | | 3 | 23| | 4 | 1004 | | 4 | 56| +++ +-+---+ For example: I want to send out a mailing on product #23 A) Customer 1001 has a bad email address, so he shouldn't be selected B) Customer 1002 has a good email address, but he's not signed up for list #2, so he shouldn't be selected C) Customer 1003 has a good email address, he's signed up for list #2, but he's ordered product #23 before, so he shouldn't be selected D) Customer 1004 has a good email address, he's signed up for list #2, and he's ordered a product that isn't #23, so he *should* be selected E) Customer 1005 has a good email address, he's signed up for list #2, and he's never ordered, so he *should* be selected Summary: > Not Selected: 1001,1002,1003 > Selected: 1004,1005 Can anyone create a query that will return the results I need. Keep in mind that I am far from being a MySQL expert, so if you start using too much technical specifics, my head might explode. ;) Thanks in advance! Jamie Jamie Tibbetts.[EMAIL PROTECTED] Epigroove .http://www.epigroove.com/ Success .http://www.success.com/ - 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 - 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
Need help with a complex query
I am currently a web designer who's taught himself the necessary basics of PHP and MySQL. I've reached a point where I can't figure out a complex query. I have a mailing list that customers can sign up for if they want to receive "Special Offer" mailings when products go on sale. So when it's time to send out a mailing on a sale product, I need to select all customers who have signed up for list #2. However, (and here's the big problem) I need to filter out the customers that have bad email addresses marked in their customer record, *and* I need to filter out the customers who have already purchased the sale product. Customers: Subscriptions: +--+---+ +++ | id | bad_email | | customerid | listid | +--+---+ +++ | 1001 | 1 | | 1001 | 2 | | 1002 | 0 | | 1002 | 1 | | 1003 | 0 | | 1003 | 2 | | 1004 | 0 | | 1004 | 2 | | 1005 | 0 | | 1005 | 2 | +--+---+ +++ Orders: Ordered_Items: +++ +-+---+ | id | customerid | | orderid | productid | +++ +-+---+ | 1 | 1001 | | 1 | 24| | 2 | 1002 | | 2 | 98| | 3 | 1003 | | 3 | 23| | 4 | 1004 | | 4 | 56| +++ +-+---+ For example: I want to send out a mailing on product #23 A) Customer 1001 has a bad email address, so he shouldn't be selected B) Customer 1002 has a good email address, but he's not signed up for list #2, so he shouldn't be selected C) Customer 1003 has a good email address, he's signed up for list #2, but he's ordered product #23 before, so he shouldn't be selected D) Customer 1004 has a good email address, he's signed up for list #2, and he's ordered a product that isn't #23, so he *should* be selected E) Customer 1005 has a good email address, he's signed up for list #2, and he's never ordered, so he *should* be selected Summary: > Not Selected: 1001,1002,1003 > Selected: 1004,1005 Can anyone create a query that will return the results I need. Keep in mind that I am far from being a MySQL expert, so if you start using too much technical specifics, my head might explode. ;) Thanks in advance! Jamie Jamie Tibbetts.[EMAIL PROTECTED] Epigroove .http://www.epigroove.com/ Success .http://www.success.com/ - 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
