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]
