Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
bemis82
Starting Member
1 Post |
 Posted - 2011-08-02 : 22:30:52
|
| 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' |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-08-02 : 22:59:30
|
tryselect transaction_process.Transaction_Number, min(transaction_Status)from transaction_process inner join transaction_Inventory on transaction_process.transaction_Number = transaction_inventory.transaction_Number where Transaction_Process.Transaction_status = 'pending' group by transaction_process.Transaction_Numberhaving min(Transaction_Status) = 'complete'and max(Transaction_Status) = 'complete'
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
|
|
|
need help writing query to select where all record