Hmm.
SELECT o.*
FROM orders o
INNER JOIN orderitems oi ON o.orderid = oi.orderid
INNER JOIN products p ON p.productid = oi.productid
AND p.productparentid > 2
WHERE o.orderstatus =2
Not sure why you are checking for a NULL ordered in orderitems? That would
suggest you get back only items that have no associated order?
The above should do what you state below, though - I think!
Cheers,
Matt
-----Original Message-----
From: Michael Collins [mailto:[EMAIL PROTECTED]
Sent: 11 April 2004 05:14
To: [EMAIL PROTECTED]
Subject: sql join help
I suppose this would be easier with subselects but I am using MySQL 4:
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
