item_status table can have more than one status. I need to get the latest status from the table. Thanks
On 11/10/05, ISC Edwin Cruz <[EMAIL PROTECTED]> wrote: > > Try it: > > select distinct a.* > from order a, > item b, > item_status c, > status d > where a.order_id = b.order_id > and b.item_id=c.item_id > and c.item_status_id = d.item_status_id > where d.status = 'completed' > > It isn´t "tunned" but I think that it works for that you want > > > -----Mensaje original----- > De: Jerry Swanson [mailto:[EMAIL PROTECTED] > Enviado el: Jueves, 10 de Noviembre de 2005 06:23 a.m. > Para: mysql@lists.mysql.com > Asunto: How to write this query? > > > 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 > > >
