In a table that displays how trades are related to each other, starting from one trade, I want to find all other trades that relate to the first trade:
713613 PostPTETradeIDs 718478
713613 PrePTETradeId 711314
718478 PostPTETradeIDs 739522
718478 PrePTETradeId 713613
In this example, trade 718478 was created by splitting 713613 (row 1); and 739522 was created from 718478 (row 3), etc. (a PTE splits a trade into two).
select trade_id, keyword_name, keyword_value
from trade_keyword
where (keyword_name like 'LinkedTo' or keyword_name like 'PrePTETradeId' or keyword_name like 'PostPTETradeIDs' )
and trade_id in('718478', '739522','713613')
order by trade_id;
My goal is to identify trades that belong together and come from one another.
Currently, using the above query, I must fill all the TRADE_IDs manually, i.e. start with 713613, run the query, see that 718478 came from it, then run the query again searching for 718478, too, to find that 739522 came from it, etc.
I would like to make this recursive, such that it will, in one query, given some starting TRADE_ID(s), find all TRADE_IDs that are related to these via any path of any of these relations.
In other words, if column 1 is X and column 3 is f(X), then I want at once a list of all X, f(X), f(f(X)), f(f(f(X))), etc.
Preferably, there would be another column that lists the distance in steps upwards or downwards, where the PostPTETradeIDs relation is upward and the PrePTETradeID is downward (the LinkedTo relationship does not change the counter):
713613 is distance 0,
718478 is distance 1,
739522 is distance 2.
I am using Oracle SQL. Is this issue better adressed via a subquery, and if so, how can it be made recursive; or some other functionality?
