SELECT DISTINCT Customer.id, Customer.name FROM Customer LEFT JOIN `Order` ON Customer.id = Order.customer_id
You now have a list of the all your Customers with and without orders.
INNER JOIN OrderLines ON Order.id = OrderLines.order_id AND OrderLines.product_id =9
You now joined the Customer/Order list with OrderLines with a product id of 9. Here is where your logic fails. You now have a list of all customers who ordered product 9. The list does not contain ANY customers without an order for product 9.
WHERE Order.customer_id IS NULL
Since you only have a list of customers who ordered product 9, you now filter out the entire result set.
Change your inner join to a left join and your query should work. MySQL will step through your query in the order you wrote, building or filtering as it goes along. You can somewhat alter this order with LEFT and/or RIGHT joins.
On Apr 21, 2005, at 10:44 AM, James Nobis wrote:
The problem is something fairly simple but yet MySQL seems to make this
complicated. Essentially, find a list of customers who have not bought product
X ever. (Customers have orders, orders have order line items). All 3 coworkers
independently arrived at the same sql which failed to work. Then, we wrote it
as a subquery which has performance issue and finally rewrote it with a temp
table and a join. However, it seems like what we had should have worked.
Borrowing from http://builder.com.com/5100-6388_14-5532304.html about midway
down the page I set out to create an identical schema and query in MySQL.
CREATE TABLE `Customer` ( `id` int(11) NOT NULL default '0', `name` varchar(255) NOT NULL default '' ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `Customer` VALUES (1, 'bob'); INSERT INTO `Customer` VALUES (2, 'nathan');
CREATE TABLE `Order` ( `id` int(11) NOT NULL auto_increment, `customer_id` int(11) NOT NULL default '0', `order_date` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
INSERT INTO `Order` VALUES (1, 1, '0000-00-00 00:00:00'); INSERT INTO `Order` VALUES (2, 2, '0000-00-00 00:00:00');
CREATE TABLE `OrderLines` ( `order_id` int(11) NOT NULL default '0', `product_id` int(11) NOT NULL default '0', `quantity` int(11) NOT NULL default '0' ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `OrderLines` VALUES (1, 5, 1); INSERT INTO `OrderLines` VALUES (1, 9, 1); INSERT INTO `OrderLines` VALUES (2, 15, 1); INSERT INTO `OrderLines` VALUES (2, 25, 1);
Then, I run the following query: SELECT DISTINCT Customer.id, Customer.name FROM Customer LEFT JOIN `Order` ON Customer.id = Order.customer_id INNER JOIN OrderLines ON Order.id = OrderLines.order_id AND OrderLines.product_id =9 WHERE Order.customer_id IS NULL
I would expect this to return a single row with Customer.id 2.
Is there something obvious my coworkers and I are missing?
James Nobis Web Developer Academic Superstore 223 W. Anderson Ln. Suite A110, Austin, TX 78752 Voice: (512) 450-1199 x453 Fax: (512) 450-0263 http://www.academicsuperstore.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- Brent Baisley Systems Architect Landover Associates, Inc. Search & Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
