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