Michael McFadden <[EMAIL PROTECTED]> wrote on 11/10/2005 08:55:13 AM:
> Hi Jerry.
>
> I'm new to the list, so don't take this as the final
> answer. Wait for a guru to pounce with a better
> solution!
>
> But, here is my idea:
>
> Before we start, note that "order" is a reserved word.
> So we must backtick `order` to reference the table in
> SQL (or the interpreter will think we're using "order
> by")
>
> Now, here's how I understand your schema:
>
> Table `order` has column 'order_id'
> Table item has columns 'order_id','item_id'
> Table item_status has columns 'item_id',
> 'item_status_id'
> Table status has columns
> 'item_status_id','status_text'
> where status_text is {"complete"|"pending"}
>
> I've given 3 queries. The last query is 'the winner'.
> The first two are there only to show the 'thinking
> path' I took. It might help you in case I didn't
> quite understand your schema.
>
> 1: (Just to the joins)
> SELECT `order`.order_id, item.item_id
> ,status.status_text
> FROM `order` INNER JOIN item USING (order_id)
> INNER JOIN item_status USING(item_id)
> INNER JOIN status USING (item_status_id);
>
> 2: (Group up by Order_id)
> SELECT `order`.order_id, item.item_id,
> group_concat(status.status_text) as "all_stats"
> FROM `order` INNER JOIN item USING (order_id)
> INNER JOIN item_status USING(item_id)
> INNER JOIN status USING (item_status_id)
> GROUP BY order_id;
>
>
> 3: "THE ANSWER" Filter out the 'pendings':
>
> SELECT `order`.order_id, item.item_id,
> group_concat(status.status_text) as "all_stats"
> FROM `order` INNER JOIN item USING (order_id)
> INNER JOIN item_status USING(item_id)
> INNER JOIN status USING (item_status_id)
> GROUP BY order_id
> HAVING all_stats NOT LIKE "%pending%";
>
>
> HTH.
>
> -Mike
>
> P.S.: Please, List.... comment and clean my SQL! I'm
> sure there is a better solution than using
> 'group_concat()' I'm here to learn too :)
>
>
> --- Jerry Swanson <[EMAIL PROTECTED]> wrote:
>
> > How to write the query?
> > table1: order (order can have more than 1 item)
> > table2: item (has order_id).
> > table3: item_status (has item_status_id and item_id)
> > table4: status (has item_status_id). Status can be
> > 'complete', 'pending'.
> >
> > I need to get all orders that have ONLY completed
> > items.
> > Examples:
> > if order has one item and it is completed, I need
> > this order.
> > If order has 2 items and both completed, I need this
> > order.
> > If order has 2 items, 1 is completed and 1 is not
> > completed, I don't need
> > this order.
> >
> > Thanks
> >
>
This will work. Good advice on backticking the ORDER names. You followed
nice logic in building your answer. The drawback is you are doing a
substring check to determine your final results, not very quick. A faster
method is to just count how many items each order has and compare to how
many items have a complete status. Number-to-number comparison is from 5
to 50 times faster than string comparisons.
Try this:
SELECT i.`order_id`
, COUNT(DISTINCT i.`item_id`) as items_this_order
, COUNT(s.`item_status_id`) as completed_items
FROM `item` i
INNER JOIN `item_status` istat
ON istat.`item_id` = i.`item_id`
LEFT JOIN status s
ON s.`item_status_id` = istat.`item_status_id`
AND s.`status`='complete'
GROUP BY i.`order_id`
HAVING items_this_order = completed_items;
The LEFT JOIN will only link in a row from `status` if the row in
`item_status` is 'complete' (look at the final ON clause). For any items
without a match, the query will return NULL as the value from any field
from the status table. COUNT() only counts non-NULL values. See the logic?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
