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
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?