RE: sql join help

[Michael Collins]

Thanks Matt. But, what I am attempting to do with (oi.orderId IS 
NULL) is to eliminate the orders that have at least one order item 
with products of product parents not equal to 2. What you show finds 
all orders that have orderitems that have a product parent greater 
than 2, however, if that orderitem is in an order that also has a 
product parent of 1 or 2, I don't want to show that order. For 
example, I have these product Ids:

1 - hats - product parent 2 (clothes)
2 - shoes - product parent 2 (clothes)
3 - water pistols - product parent 3 (toys)
4 - rubber chickens - product parent 3 (toys)
And order ids:

222 has: 1 and 3 product Ids, o.orderstatus =2
333 has: 3 and 4 product Ids , o.orderstatus =2
What you propose would find both order id 222 and 333 since both 
contain a product Id greater than 2. What I want is to only find 
those orders that do not have 1 or 2, in other words just order id 
333 (from the two examples shown above).

Help would be greatly appreciated!

Michael

At 11:10 AM +0100 4/11/04, Matt Chatterley wrote:
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]


--
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]