Some of these tables are rather large, I would put the schema in there, but I think it just adds to the mess.
Tables `users` field id is auto inc pk `orders` field id is auto inc pk `products` field id is auto inc pk `order_items` field order_id is the key for hooking up order_items to orders, also, in `order_items` I store order_id which comes from the users table. I also have product_id which is how the product is related to an order item. Some of the products can be marked as instock or out of stock, with products.ship_status = 1 or 0 What I need, is a select, that grabs all order records, joined on the order_items for that order, where the products.ship_status is = 1. If ANY of the products do not have a ship_status of 1, it should not be include in the results. I also need to further limit this based on a date field in the orders table, which can be a date in the future, or NULL, it is called delayed_ship_date. So the limit is WHERE delayed_ship_date is within 13 days of NOW() OR where delayed_ship_date is null. Finally, only where the order status is either 'pre-order' or 'delayed' To better explain, we have orders in a system, each order can have many order items. However, each of the order items may or may not be in stock. I only want to see orders that can be fulfilled. I further want to only see orders where the delayed_ship_date is within 13 days of today, or where it is NULL, in which case, it means the user wants the stuff as soon as possible. I know it is a little strange, we are dealing with perishables, so random shipping dates do not always apply. I seem to be able to get this to sort of work, but I end up getting back thousands of records where I want just one for the order, not one for each and ever order item. -- ------------------------------------------------------------- Scott Haneda Tel: 415.898.2602 <http://www.newgeo.com> Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
