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
