SELECT "False" if any records are false?

[Azurewrath]

Using fake data and field names, but this is the issue I am having.

OrderID: Status:
1 Complete
1 Incomplete
2 Complete
3 Incomplete
4 Incomplete
4 Incomplete


5 Complete
5 Complete

I want a query that would return:
OrderID: Status:
1 Incomplete
2 Complete
3 Incomplete
4 Incomplete
5 Complete

If any status for a particular OrderID is false, status should be false.

Status is actually the result of a calculation, so I could even build this query logic around how status is generated. There are hundreds of records so runtime is an issue.

Thank you!

[orange]

Why are there multiple statuses for the same OrderId?

[Azurewrath]

Why are there multiple statuses for the same OrderId?

Good question, I need to use a better example.

Basically, I have 3 tables, let's call them Restaurant, Order, and Item

A restaurant can take several orders, and each order can have several items.

The items for the same order can be completed at different times.

I want a query of Restaurant.RestaurantName, as well as order status. Order status is complete if all Items are complete, but it will be incomplete if even one Item is incomplete. I just don't really know how to structure the query or what functions to use.

Edit: A restaurant doesn't have to have any orders, but an order has to have at least 1 item. I don't think this really changes the query I need, but thought I'd mention it.

[Azurewrath]

I think what I need to do is select orders where items are incomplete. Then, any items that aren't selected must be complete, but I don't know how to effeciently see what orders haven't been selected yet. How do I do this?

[orange]

Yes, you are correct.
I created a few tables and populated them with some test data. Then created a query to Select only the Orders where all details are Complete. I realize you want to select all that are incomplete. So you can see the query sql and adjust it accordingly to do what you want.
The tables and query are shown in the attached jpgs.
Post back if there are questions.

[Azurewrath]

Wow, thank you very much orange. I am grateful and impressed by the amount of effort and detail you put into your response!

I set up my query the way you set up yours, and it works great (I struggled with the "IN" function prior to your post).

My only issue now is that I need one comprehensive list of both complete AND incomplete, but this design only allows me to select one or the other. Is creating a UNION query the only way around this issue?

[orange]

This is quick but will work. It is a UNION query

SELECT trestaurant.RName, trestaurant.id, ROrder.OrderNo,"Complete"
FROM trestaurant INNER JOIN ROrder ON trestaurant.id=ROrder.RestId
WHERE (ROrder.OrderNo) Not In (Select orderNo from ROrderDetails where Completed = 0)
GROUP BY trestaurant.RName, trestaurant.id, ROrder.OrderNo
Union
SELECT trestaurant.RName, trestaurant.id, ROrder.OrderNo,"Incomplete"
FROM trestaurant INNER JOIN ROrder ON trestaurant.id=ROrder.RestId
WHERE (ROrder.OrderNo) In (Select orderNo from ROrderDetails where Completed = 0)
GROUP BY trestaurant.RName, trestaurant.id, ROrder.OrderNo