I have 2 tables:

1. ORDERS:

order_id
--------
1
2
3


2. ORDER_PRODUCTS

order_product_id | order_id | status

76---------------------1--------------ok
77---------------------1--------------ok
78---------------------1--------------ok
80---------------------2--------------ok
82---------------------2-------------xxx
82---------------------2--------------ok
83---------------------3--------------ok
84---------------------3--------------ok

What I need:

I need to get all distinct orders from orders table with any status in
order_products table, BUT don't take an order if it has status "ok" AND
appears in order_products table exactly 3 times.

Here is the same but in other words:

I need to get all distinct orders from orders table,
EXCEPT of those for which the number of order_products with
status "ok" is equal to 3 AND for which there are no any other
statuses rather then OK.

Please help! I have no problems with a query that gives me exactly the orders I DON'T need:

select distinct orders.*, count(order_products.order_product_id) from orders
left join order_products on order_products.order_id = orders.order_id
where
order_products.status = 'OK'
group by orders.order_id
having count(order_products.order_product_id) = 3
order by orders.order_id

Thanks a million for help!
P.S. I can't use sub-selects (don't have MySql 4.1)

Try something like


select distinct orders.*, count(order_products.order_product_id) AS NbOrder from orders
left join order_products on order_products.order_id = orders.order_id AND order_products.status = 'OK'

group by orders.order_id
having NbOrder <> 3
order by orders.order_id

Olivier, this question is almost certainly someone's homework, and you do the questioner a disservice by not following sql standards

any time you have a GROUP BY, it must include all non-aggregate columns in the SELECT list

in this particular example, when you have orders.* in the SELECT list but only orders.order_id in the GROUP BY, you are fortunate, because the orders table appears to have only one column in it!

in general you cannot use * in the SELECT list unless you itemize each and every column in the GROUP BY

only mysql lets you get away with "hidden" group by fields (do a search on mysql.com for "group by hidden")

finally, i just wanted to point out that DISTINCT with GROUP BY is unnecessary (and wasteful), since groups are, by definition, unique

Sorry just fast a cut and past from the previus query
distinct is not usefull here

"I need to get all distinct orders from orders table, EXCEPT of those for which the number of order_products with status "ok" is equal to 3 AND for which there are no any other statuses rather then OK."

Thanks to all! Rudy, I already mentioned in devshed forum (http://forums.devshed.com/t138173/s.html) it wasn't a homework, but just simplified, rephrased version of what I really need.

I'm sure the last query your wrote is defenitely gonna work! Thank you so much! And I promse will never use * with group by!
Thanks again!