-
need help writing query to select where all records in group meet criteria
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'
-
select TP.Transaction_Number
From transaction_process as TP
Where TP.Transaction_status = 'pending'
and not exists (
select 1 from transaction_Inventory as TI
where TP.transaction_Number = TI.transaction_Number
And TI.Transaction_Item_Status <> 'complete'
)
-
Thanks man, works like a charm!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|