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.
What I think I need is a subselect and the IN clause, but that is not available in MySQL 4.
-- 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]
