I want all orders that are of orderStatus 2 and whose orderitems contain a product that is in a productparent category greater than 2. An orderitem can only have one product, and that product has a single certain product parent (defined in the products table). This is how the tables are related:
members -< orders -< orderitems >- products >- productparents
I have tried the following, but I know it is not correct:
SELECT count(*) FROM orders AS o
LEFT JOIN members AS m USING (memberId)
LEFT JOIN orderItems AS oi ON (o.orderId=oi.orderId)
LEFT JOIN products AS p ON (oi.productId=p.productId) AND (p.productParentId > 2)
WHERE (oi.orderId IS NULL) AND (o.orderStatus=2);
-- Michael __ ||| Michael Collins ||| Kuwago Inc mailto:[EMAIL PROTECTED] ||| Seattle, WA, USA http://michaelcollins.net
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
