Page 1 of 1

mysql Select with condition depending on more than one row

Posted: Wed Oct 25, 2006 2:50 am
by Akii
Ok - this is not that easy to explain, but i will try anyway.
I would like to make a select that discards all rows with a certain id if ONE row has a specific value.
It would make my life so much easier if that was possible, though i fear that it is not.
I have two tables - one with orders and one with the status of these orders. Status can be watched or accepted. One order can only be "accepted" once but be "watched" more than once.

Now i want to Select a list of all orders that are NOT accepted. So i make a left join of that orders table with the orders_status table.

For an order with one "watched" and one "accepted" that would give me two rows - one with the accepted-status and one with the watched-status. Just adding the condition
WHERE orders_status.status!='accepted' discards one of those rows, but not the one with the status "watched".

So for an order that is was being watched AND is accepted i still get one row in the result with the status "watched", which causes the order to appear in the final result.
Which i didn't want in the first place.

This is my select atm:
SELECT orders.*,orders_status.* FROM orders LEFT JOIN orders_status ON orders.id=orders_status.order_id WHERE ( orders_status.status!='accepted' ) OR orders_status.status IS NULL GROUP BY orders.id

So what i would need is a solution that comes down to
Select all orders with their status and discard ALL rows of an order if one of those rows has the status "accepted".

Of course i could loop through the result and discard those orders that have one accepted-status after the select, but then i get problems with LIMIT. And if i select the orders first and the status afterward and then loop trough the array later and discard the accepted orders i also get problems with my LIMIT.

So any thoughts on how to do this in one select would really be appriciated.

Posted: Wed Oct 25, 2006 6:57 am
by volka
You can adopt the tips from http://dev.mysql.com/doc/refman/5.1/en/ ... p-row.html
I still prefer the method under
Posted by Csaba Gabor on March 16 2003 8:16am