Re: possible bug left join and null
Thanks everyone for such quick and thorough responses! Quoting [EMAIL PROTECTED]: James Nobis <[EMAIL PROTECTED]> wrote on 04/21/2005 10:44:07 AM: 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 '-00-00 00:00:00', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ; INSERT INTO `Order` VALUES (1, 1, '-00-00 00:00:00'); INSERT INTO `Order` VALUES (2, 2, '-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 It's hard to remember where I picked this up but I once read that it's generally bad form to start with an outer join (LEFT or RIGHT JOIN) and move into an INNER JOIN like you are doing. Because if the rows from the Order table are optional to the results of the query, the rows from the OrderLines are transitively optional as well (if an Order row doesn't exist then there can't be any OrderLine rows either). So an equivalent form of your query could have been: SELECT DISTINCT Customer.id, Customer.name FROM Customer LEFT JOIN `Order` ON Customer.id = Order.customer_id LEFT JOIN OrderLines ON Order.id = OrderLines.order_id AND OrderLines.product_id =9 WHERE Order.customer_id IS NULL; But this won't help you to determine if a Customer had NEVER ordered that product because you are including Order rows regardless of whether that order had a product #9 on it or not. I then tried a nested JOIN using parentheses like this and got no names: SELECT DISTINCT Customer.id, Customer.name FROM Customer LEFT JOIN (`Order` INNER JOIN OrderLines ON Order.id = OrderLines.order_id AND OrderLines.product_id =9 ) ON Customer.id = Order.customer_id WHERE Order.customer_id IS NULL; The unfiltered results of that join look like this(sorry if it wraps): SELECT * FROM Customer LEFT JOIN ( `Order` INNER JOIN OrderLines ON Order.id = OrderLines.order_id AND OrderLines.product_id =9 ) ON Customer.id = Order.customer_id; ++++-+-+--++--+ | id | name | id | customer_id | order_date | order_id | product_id | quantity | ++++-+-+--++--+ | 1 | bob| 1 | 1 | -00-00 00:00:00 |1 | 9 | 1 | | 2 | nathan | 1 | 1 | -00-00 00:00:00 | NULL | NULL | NULL | | 1 | bob| 2 | 2 | -00-00 00:00:00 | NULL | NULL | NULL | | 2 | nathan | 2 | 2 | -00-00 00:00:00 | NULL | NULL | NULL | ++++-+-+--++--+ 4 rows in set (0.00 sec) Each customer has at least one order so the nested JOIN didn't work to find your answer either (BTW- nested joins are not documented as a valid syntax so I wasn't sure if it was going to work or not). However, I thought, why not do exactly what the original question stated: count how many times product 9 appears as a line item on an order and return the names of the customers where that count is 0. SELECT Customer.id , Customer.name , COUNT(orderlines.product_id) as Line
Re: possible bug left join and null
There is nothing wrong with what MySQL is doing. Your query is incorrect for what you are looking for. Step through your query and you'll see your error. 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 '-00-00 00:00:00', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ; INSERT INTO `Order` VALUES (1, 1, '-00-00 00:00:00'); INSERT INTO `Order` VALUES (2, 2, '-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]
Re: possible bug left join and null
James Nobis <[EMAIL PROTECTED]> wrote on 04/21/2005 10:44:07 AM: > 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 '-00-00 00:00:00', > PRIMARY KEY (`id`) > ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ; > > INSERT INTO `Order` VALUES (1, 1, '-00-00 00:00:00'); > INSERT INTO `Order` VALUES (2, 2, '-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 > It's hard to remember where I picked this up but I once read that it's generally bad form to start with an outer join (LEFT or RIGHT JOIN) and move into an INNER JOIN like you are doing. Because if the rows from the Order table are optional to the results of the query, the rows from the OrderLines are transitively optional as well (if an Order row doesn't exist then there can't be any OrderLine rows either). So an equivalent form of your query could have been: SELECT DISTINCT Customer.id, Customer.name FROM Customer LEFT JOIN `Order` ON Customer.id = Order.customer_id LEFT JOIN OrderLines ON Order.id = OrderLines.order_id AND OrderLines.product_id =9 WHERE Order.customer_id IS NULL; But this won't help you to determine if a Customer had NEVER ordered that product because you are including Order rows regardless of whether that order had a product #9 on it or not. I then tried a nested JOIN using parentheses like this and got no names: SELECT DISTINCT Customer.id, Customer.name FROM Customer LEFT JOIN (`Order` INNER JOIN OrderLines ON Order.id = OrderLines.order_id AND OrderLines.product_id =9 ) ON Customer.id = Order.customer_id WHERE Order.customer_id IS NULL; The unfiltered results of that join look like this(sorry if it wraps): SELECT * FROM Customer LEFT JOIN ( `Order` INNER JOIN OrderLines ON Order.id = OrderLines.order_id AND OrderLines.product_id =9 ) ON Customer.id = Order.customer_id; ++++-+-+--++--+ | id | name | id | customer_id | order_date | order_id | product_id | quantity | ++++-+-+--++--+ | 1 | bob| 1 | 1 | -00-00 00:00:00 |1 | 9 | 1 | | 2 | nathan | 1 | 1 | -00-00 00:00:00 | NULL | NULL | NULL | | 1 | bob| 2 | 2 | -00-00 00:00:00 | NULL | NULL | NULL | | 2 | nathan | 2 | 2 | -00-00 00:00:00 | NULL | NULL | NULL | ++++-+-+--++--+ 4 rows in set (0.00 sec) Each customer has at least one order so the nested JOIN didn't work to find your answer either (BTW- nested joins are not documented as a valid syntax so I wasn't sure if it was going to work or not). However, I thought, why not do exactly what the original question stated: count how many times product 9 appears as a line item on an order and return the names of the customers where tha
Re: possible bug left join and null
From: "James Nobis" > 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 expect customers to have placed at least one order, or can one have customers which have not a single order? Do you want these "customers" included in the output? Anyway, I expect that you want the order of all customers checked; this can be done with a (inner) join: `Customer` JOIN `Order` ON `Customer`.`id` = `Order`.`customer_id` Then you left-join this with the order lines to find out all the products and check for an empty order id: SELECT DISTINCT Customer.id, Customer.name FROM Customer JOIN `Order` ON Customer.id = Order.customer_id LEFT JOIN OrderLines ON Order.id = OrderLines.order_id AND OrderLines.product_id =9 WHERE OrderLines.order_id IS NULL This returns: +-+---+ | Customer.id | Customer.name | +-+---+ | 2 |nathan | +-+---+ Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
