* Michael Collins
> Using the latest MySQL 4.
>
> I could use some help on performing a join but not retrieving all the
> records of the child table. I have a set of tables, the ones involved
> in this are related as follows:
>
> members (one) -< (many) orders (one) -< (many) orderitems
>
> The end result should be a list of orders with only one row for each
> order. However, the criteria for the search requires me to join to
> the orderitems table, since I want to find all orders that do not
> have a certain product in that order. Here is the basic SQL statement
> I envision:
>
> SELECT * FROM orders AS o
> LEFT JOIN members AS m USING (memberId)
> LEFT JOIN orderItems AS oi ON o.orderId=oi.orderId
> WHERE (o.orderStatus=2) AND
> (oi.productId != 55)
>
> (BTW, I don't actually use SELECT * , just using it here for the
> purposes of this example)
>
> I still get orders that contains orderitems with productId 55, since
> there are other order items in the order that are not that product.
> So Distinct does not help here.
Right, but you can reformulate the query slightly to utilize the special
ability of the LEFT JOIN to return NULL when there is no match:
SELECT * FROM orders AS o
LEFT JOIN members AS m USING (memberId)
LEFT JOIN orderItems AS oi ON
o.orderId=oi.orderId AND
oi.productId = 55
WHERE
o.orderStatus=2 AND
oi.orderId IS NULL;
You search for the orderitems you _don't_ want, and use IS NULL (or similar)
in the where clause to eliminate results where an orderitem was found.
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
