How to build a sequence of values by combining two columns?-CodePudding

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

CodePudding user response：

you are going to have to use a cursor.. https://www.mysqltutorial.org/mysql-cursor/

first step will be to select the value from col2 that doesn't exist in col3 then insert the value from col3 where the current variable is in col2 return the results set when the value in col3 is not found in col2

This will only work if there is one start and end value and one distinct path through the chain. It will also be slow, because this is not how RDBMS databases are designed to work.

CodePudding user response：

I think this query will work for you.

SELECT DISTINCT SEQ 
FROM 
(
  SELECT COL2 SEQ FROM TABLE1 
  UNION 
  SELECT COL3 SEQ FROM TABLE1
) ORDER BY 1