0

I have a transaction network in DB2,which like below:

Table:trans

Originator  Beneficiary   Amount
A            B              100.00
C            D              150.00
B            F              400.00
D            Z              30.00

Let's say, we know A and C is the originator of transaction. Now I select * from trans where Originator in (A,C) Then I select * from trans where Originator in (select * from trans where Originator in (A,C)

Is there any method to tracing all counterpart in a single SQL? And is there better method to trace money?

Siyual
  • 16,415
  • 8
  • 44
  • 58
C HAN
  • 49
  • 4
  • Your queries don't make any sense - you're selecting everything from `trans` where the `Originator` is in the resultset containing... records in `trans` where the `Originator` is either `A` or `C`... seems rather redundant, no? – Siyual Mar 02 '17 at 18:48
  • Can you please show what your expected results are? I can't follow what you're trying to do. – Siyual Mar 02 '17 at 18:49
  • 1
    Sound like you need a rekursve solution like in http://stackoverflow.com/questions/25963799/recursive-query-in-db2-to-get-all-items-in-the-chain – MichaelTiefenbacher Mar 02 '17 at 19:31
  • to Syyual,thank you for your quick response and I am sorry for my description on my question. Actually, I want to select all counterpart in a single SQL. My expected results like below: A-->B--->F--->.....;B--->C--->D--->Z; C-->/.... If given A for input, I want to get all transactions related to A and all the counterparts in my database.Now I just – C HAN Mar 06 '17 at 04:11
  • select Beneficiary from the table using 'select Beneficiary from trans where Originator =A'; THEN I got B, and using select Beneficiary from trans where Originator =B' to get F,and so on. Any solution would be appreciated. Thank you. – C HAN Mar 06 '17 at 04:20
  • The expected result would be ,for A, Originator Beneficiary Amount A B 100.00 B F 400.00 – C HAN Mar 06 '17 at 06:32

0 Answers0