Results 1 to 3 of 3

Thread: need help writing query to select where all records in group meet criteria

  1. #1
    Join Date
    Aug 2011
    Posts
    2

    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'

  2. #2
    Join Date
    Aug 2011
    Posts
    1
    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'
    )

  3. #3
    Join Date
    Aug 2011
    Posts
    2
    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
  •