MySQL query suggestion needed

Question

I have three tables: Orders, OrderAdditionalInfo, Invoices

I need to select (main) orders that has no valid (or uncancelled) invoice.

Fields:

Orders ->

OrderID int(11)

OrderAdditionalInfo ->

DetailID int(11)
OrderID int(11)
Variable varchar(255)
Value varchar(255)

Invoices ->

InvoiceID int(11)
OrderID int(11)
Cancelled tinyint(4) //if cancelled is 1 then this invoice is invalid.

IMPORTANT: The thing that confuses everything is: One order can be divided into multiple orders.

For example:

When we divide an order with an OrderID: 10

1- Order 10 remains as the main order and a new order with ID 11 is added to the table. (We gain a new order.)

2- Unfortunately, a row is added to OrderAdditionalInfo table:

OrderID: 11
Variable: "Main Order ID"
Value: "10"

3- So, we can know the order 11 has been derivered from order 10.

Do you have any suggestions about selecting orders that matches:

"main order" (is not derivered from another order)

and

"has no valid invoice"

Answer 1

select * from orders o where o.OrderId not in (select OrderId from Invoices) and o.OrderId not in (select OrderId from OrderAdditionalInfo oi where oi.Variable = 'Main Order ID')