Using Distinct in SQL Query

[mike718]

I have a table with transactions. My transactions have 3 main fields that I am concerned with: Transaction number, Status, and Acct Number. If a transaction was made and then cancelled it would look like this:

Trans# Status Acct Number
0001 Processed 12345
0001 Cancelled 12345
0002 Processed 12345

Trans 0002 is a new transaction made under that account.

I have to run a query for all of Transactions that have been processed and then canceled on this table. So I would just get:

Trans# Status Acct Number
0001 Processed 12345
0001 Cancelled 12345


I was thinking I could do something like

Select Not Distinct Trans
Fron TransTable

but that does not work. Can someone please assist me with this problem? Thanks

Mike

 

[dbomrrsm]

select Trans#, Status, [Acct Number]
from yourtable
where Trans# In
(select trans# from yourtable where status = 'cancelled')
and [Acct Number] in
(select [Acct Number] from yourtable where status = 'cancelled')

[blue]DBomrrsm[/blue]

 

[SQLSister]

Select TransactionNo, AcctNumber from TransTable where Status = 'cancelled'

Or

Select t.TransactionNo, t.Status, t.AcctNumber from
Transtable t Join
(Select TransactionNo, AcctNumber from TransTable where Status = 'cancelled') a
on t.TransactionNumber = a.TransactionNumber
And t.AcctNumber = a.AcctNumber

The first will get just one row per transaction, the second will give you both the processed and cancelled rows.



Questions about posting. See faq183-874

 

[dbomrrsm]

sorry:

select Trans#, Status, [Acct Number]
from yourtable
where [Acct Number] In
(select [Acct Number]
from yourtable
where status = 'cancelled')

[blue]DBomrrsm[/blue]

 

[mike718]

Thank you all for the feed back. DBomrrsm, your first posting did the trick. I just had to change one thing and it ran fine. I completly forgot about using IN.

Mike