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]
