Hey everyone, i have a two tables (transaction_process and transaction_inventory) they both have the field called transaction_number however in the transaction_process table it is unique and in the Transaction_inventory table it is not because each transaction could have multiple items in its inventory. Also in the transaction_inventory table is a field called transaction_item_status. This field specifies whethe that inventory item is complete or pending. I'm trying to write a query where i will see only the transaction numbers where all of the items in the transaction_inventory.transaction_item_status field = complete. If even 1 of them is not complete i don't want to see the transaction number. below is the query i'm working with, but it still shows the transaction numbers if some but not all are complete which is not what i want.

Thanks for the help.

select Distinct transaction_process.Transaction_Number, Transaction_Status From transaction_process inner join transaction_Inventory ON transaction_process.transaction_Number = transaction_inventory.transaction_Number where Transaction_Process.Transaction_status = 'pending' And Transaction_Item_Status = 'complete'