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
>
__________________________________
Yahoo! FareChase: Search multiple travel sites in one click.
http://farechase.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
