[SOLVED] How to build a sequence of values by combining two columns?

Issue

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

Explanation:

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.

Solution

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)