CODEWITHSUNDEEP
sqlt-sqlsql-server-2005
Misiu
Misiu

Reputation: 4919

SELECT all orders with more than one item and check all items status

I have 2 tables:

Orders:

Id   |  Status
-----+--------
 1   |      OK
 2   | WAITING
 3   | WAITING
 4   |      OK
 5   |      OK
 6   |      OK

And Order_details:

Id   |  Order_Id  |  Status
-----+------------+--------
 1   |         1  |      S1
 2   |         1  |      S1
 3   |         2  |      S1
 4   |         4  |      S2
 5   |         4  |      S2
 6   |         4  |      S3
 7   |         5  |      S1
 8   |         5  |      S1

What I need to do is to select all orders having more than one Order_detail and Order status must be 'OK' and every order_detail must have status in (S1,S2)

I've done something like this:

SELECT O.Id FROM Orders O 
            JOIN Order_details OD 
                ON O.Id=OD.Order_Id
WHERE O.Status='OK' AND OD.Status IN ('S1','S2')
GROUP BY O.Id
HAVING count(DISTINCT OD.Id)>1

But this returns all Orders that have more than one Order_details meeting criteria.

I'm sure that this part is fine:

SELECT O.Id FROM Orders O 
            JOIN Order_details OD 
                ON O.Id=OD.Order_Id
WHERE O.Status='OK'
GROUP BY O.Id
HAVING count(DISTINCT OD.Id)>1

But I must check if every Order_detail for above orders has Status IN (S1, S2).

So if Order will have 2 details and one of them has status=S1 and second S3 this order should be skipped.

Upvotes: 0

Views: 4089

Answers (2)

Simon Thompson
Simon Thompson

Reputation: 704

select O.id
from orders O inner join order_details OD
on O.Id=OD.Order_Id and O.Status='OK' AND OD.Status IN ('S1','S2')
GROUP BY O.Id
HAVING count(DISTINCT OD.Id)>1

Upvotes: 1

juergen d
juergen d

Reputation: 204904

SELECT O.Id 
FROM Orders O 
JOIN Order_details OD ON O.Id=OD.Order_Id
WHERE O.Status='OK'
GROUP BY O.Id
HAVING count(DISTINCT OD.Id)>1
AND SUM(CASE WHEN OD.Status NOT IN ('S1','S2') 
             THEN 1 
             ELSE 0 
        END) = 0

Upvotes: 2

Related Questions