Write SQL query that takes its own output as input

oracle-sql-developerqueryrecursiveself-join

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?

Best Answer

You can use a subquery with the same table in both the primary and sub queries, join on trade_id = keyword_value

Depending on how many levels a trade could have it might get complex, but this should get you going in the right direction.