SQL Advanced SELECT query question

I have three tables, which, among other things contain:

CustomerOrder
---
idOrder
TableNumber

OrderStatus
--------
Menu
idMenu
Name

MenuItemOrdered
------------
idMenuItemOrdered
MenuID
OrderID
TableNumber

I want to retrieve the following result:

Menu.Name CustomerOrder.TableNumber CustomerOrder.OrderStatus
-------------------------------------------------------------
Fish            1               New
Chicken         1               New
Steak           1               New
Steak           2               New
Steak           2               New
Steak           2               New
Steak           2               New

I've come up with:

SELECT Menu.Name, CustomerOrder.TableNumber, CustomerOrder.OrderStatus 
FROM Menu,开发者_如何学JAVA CustomerOrder 
WHERE Menu.idMenu IN (SELECT MenuID FROM MenuItemOrdered) 
AND CustomerOrder.OrderStatus = "New" OR CustomerOrder.OrderStatus = "Cooking"
ORDER By CustomerOrder.TableNumber

But, I don't feel quite confident on that answer. Any clues how to pull this off?

This should work

SELECT Menu.Name, CustomerOrder.TableNumber, CustomerOrder.OrderStatus 
FROM CustomerOrder
JOIN menuitemordered on customeorder.idorder = menuitemordered.orderid
JOIN menu on menuitemordered.menuid = menu.idmenu
WHERE CustomerOrder.OrderStatus = "New" OR CustomerOrder.OrderStatus = "Cooking"
ORDER By CustomerOrder.TableNumber

I think it is really strange that the menu id is called idmenu in one table and menuid in another (this is true of order id too!)

I do think it's homework, but in addressing the question as asked, I will simply point out that by giving an incomplete spec of the tables (for example, I am sure there is a CustomerOrder.idCustomer, and a table called 'Table', etc), you leave us in the position of guessing at what should be added to or subtracted from your query. Somewhere in your textbook, there is a section on joins, which you should review.