List:General Discussion« Previous MessageNext Message »
From:Brent Baisley Date:April 21 2005 3:54pm
Subject:Re: possible bug left join and null
View as plain text  
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 '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/mysql?unsub=1
>
>
-- 
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577

Thread
possible bug left join and nullJames Nobis21 Apr
  • Re: possible bug left join and nullJigal van Hemert21 Apr
  • Re: possible bug left join and nullSGreen21 Apr
    • Re: possible bug left join and nullJames Nobis21 Apr
  • Re: possible bug left join and nullBrent Baisley21 Apr