I have a table sample below:
Table_A
col1 | col2 |
---|---|
881 | 113 |
988 | 899 |
113 | 765 |
765 | 765 |
122 | 881 |
300 | 400 |
765 | 910 |
910 | 345 |
999 | 988 |
What I want to achieve here, is to get the last chaining data based on table above with the requirement as the following, each data must find their chain data based on col2 to col1 until the data not find any chain and ignore the data with the same value for example in this case for the col1 = 122 and col2 = 881 should be 122>881>113>765>910>365. For the data that does not have any data chain or data with the same value could be leave it in blank or null.
I try to do self-join, but it only accommodates 1 time chain only. I am looking for dynamic chain based on the number of data itself.
SELECT A.col1,
A.col2,
B.col1
FROM Table_A AS A
LEFT JOIN Table_A AS B
The expecting result as follow:
col1 | col2 | last_chain_data |
---|---|---|
881 | 113 | 345 |
988 | 899 | NULL |
113 | 765 | 345 |
765 | 765 | NULL |
122 | 881 | 345 |
300 | 400 | NULL |
765 | 910 | 345 |
910 | 345 | NULL |
999 | 988 | 899 |
is there any method to achieve this in MySQL?
2
Answers
Use recursive CTE:
fiddle
Another recursive query example
Output is
About (765, 765)–>null – why the result should be like this should be further described in the question. I don’t want to guess. In this case, the result may be ambiguous.
fiddle