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
