I am looking to get a sequence of values by combining two columns that are linked using some random ids:
Table (col2 and col3 are linked)
col1 col2 col3 aa a144 d653 bb z567 a144 cc d653 h999 dd y678 z567
The two columns (col2 and col3), this is like a chain that is forming up.
The result I am looking for is a sequence from start to end:
sequence y678 z567 a144 d653 h999
The sequence starts at row 4 (dd,y678,z567), followed by row 2 (bb,z567,a144) and so on.
Col3 id is the reference for the Col2 id, to decide the next element.
What you’re looking for is a recursive query.
Assuming your table is called
data, you do it like this:
WITH RECURSIVE query(id) AS ( SELECT col2 FROM data WHERE col1 = 'dd' -- Select the initial row here UNION ALL SELECT data.col3 FROM data INNER JOIN query on query.id = data.col2 ) SELECT * FROM query;
Tested snippet available here: https://onecompiler.com/mysql/3xvj2a47v.
This syntax works in MySQL version 8 and up. If your version is lower, first thing I would recommend is to update it, if possible. If not possible, consult this answer for a workaround using MySQL 5: https://stackoverflow.com/a/33737203/2979473.
Answered By – Robert Synoradzki
Answer Checked By – Marie Seifert (BugsFixing Admin)