Search code examples
mysqlselectmariadb

Query chain data in MySQL

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?

Solution

  • Use recursive CTE:

    WITH RECURSIVE
cte1 AS (
  SELECT col1, col2, CAST(col2 AS CHAR(65535)) path, 1 level
  FROM test
  UNION ALL
  SELECT cte1.col1, cte1.col2, CONCAT_WS(',', cte1.path, test.col2), cte1.level + 1
  FROM test
  JOIN cte1 ON FIND_IN_SET(test.col1, cte1.path)
  WHERE NOT FIND_IN_SET(test.col2, cte1.path)
  ),
cte2 AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY LENGTH(path) DESC) rn
  FROM cte1
  )
SELECT col1, col2, SUBSTRING_INDEX(path, ',', -1) last_chain_data
FROM cte2 
WHERE rn = 1
ORDER BY last_chain_data, level DESC;
    col1 col2 last_chain_data
    122 881 345
    881 113 345
    113 765 345
    765 765 345
    765 910 345
    910 345 345
    300 400 400
    999 988 899
    988 899 899

    fiddle