Scott Haneda <[EMAIL PROTECTED]> wrote on 11/16/2005 05:44:05 AM:
> 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.
>
>
## First, let's identify which products have a ship_status=1
CREATE TEMPORARY TABLE tmpProducts (KEY(id)) SELECT id
FROM products p
WHERE ship_status=1;
## Now let's make a list of all orders that meet your date criteria and
shipping status
CREATE TEMPORARY TABLE tmpOrders (KEY(ID)) (
SELECT id
FROM orders
WHERE delayed_ship_date < = NOW() + INTERVAL 13 DAY
AND status IN ('pre-order','delayed')
) UNION (
SELECT id
FROM orders
WHERE delayed_ship_date is NULL
AND status IN ('pre-order','delayed')
);
## Let's combine those tables with order_items to single out only those
orders that
## not only meet your order status and date criteria but also have
products
## whose ship_status=1.
CREATE TEMPORARY TABLE tmpBackorders (KEY(ID)) SELECT DISTINCT ord.id
FROM tmpOrders ord
INNER JOIN order_items oi
on oi.order_id = ord.id
INNER JOIN tmpProducts tp
on tp.id = oi.product_id;
## now you have a list of all of the backorders that should be shipped
ASAP or within 13 days
## You can use that list to get all of the information for each order back
from the
## `orders` table
SELECT o.*
FROM orders o
inner join tmpBackorders b
on b.id = o.id
order by o.delayed_ship_date;
## Last thing: the database is not your mommy. Clean up before you leave
the session
DROP TEMPORARY TABLE tmpBackorders, tmpOrders, tmpProducts;
Make sense? I split it up this way for speed. To do this all in one
complex statement _IS_ possible (I know I can write a single statement to
return the same results) but you didn't sound like you had an hour to wait
on that query to finish. The extra time it would have taken to process the
single-statement version has to do with how many JOINs were going to be
used and how much of your existing data you would wind up throwing out
anyway. By pre-limiting ourselves to selected subsets of your data, we
drastically minimize the number of comparisons necessary to compute
tmpBackorders thus reducing how long it takes to give you the desired
information from the orders table.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
