sql - @ Symbol - a solution for Recursive SELECT query in Mysql? -

there lot of questions recursive select query in mysql, of answers "there no solution recursive select query in mysql".

actually there solution & want know clearly, question following of previous question can found @ (how-to-do-the-recursive-select-query-in-mysql)

suppose have table:

col1 - col2 - col3 1    -    -  5 5    -  d   -  3 3    -  k   -  7 6    -  o   -  2 2    -  0   -  8 

& want find links connect value "1" in col1, i.e. want print out:

1 - - 5 5 - d - 3 3 - k - 7 

then can use simple query:

select col1, col2, @pv:=col3 'col3' table1 join (select @pv:=1)tmp col1=@pv 

ok, good, however, if table has 2 records containing "1" in col1 & 2 records containing "3" in col1, ex:

col1 - col2 - col3 1    -    -  5 1    -  m   -  9 5    -  d   -  3 3    -  k   -  7 6    -  o   -  2 3    -  v   -  10 2    -  0   -  8 

then, when users search "1" in col1, should show links connecting 2 "1", i.e. should show expecting result:

col1 - col2 - col3 1    -    -  5 1    -  m   -  9 5    -  d   -  3 3    -  k   -  7 3    -  v   -  10 

so, question how modify above query show links in above expecting result?

edit: @ gordon, if omit select distinct col1, col2 from query means something, can work on (since childid got increased, can order table1 ):

select col1, col2,          @pv:=(case when find_in_set(col3, @pv) @pv else concat(@pv, ',', col3)                 end) 'col3'   (select * table1 order col1) tb1 join       (select @pv:='1') tmp       on find_in_set(col1, @pv) > 0 

in case, don't worry order, example, if data:

col1 - col2 - col3 4    -    -  5 1    -  d   -  2 1    -  k   -  4 2    -  o   -  3 6    -  k   -  8 8    -  o   -  9 

the output be:

col1 - col2 - col3 1    -  d   -  1,2 1    -  k   -  1,2,4 2    -  o   -  1,2,4,3 

so result 1,2,4,3 right? & select records if col1 in 1,2,4,3. can final expected result.

if case, can think of special case rules out solution mentioned?

i keep wondering if work:

select distinct col1, col2 (select col1, col2,              @pv:=(case when find_in_set(col3, @pv) @pv else concat(@pv, ',', col3)                     end) 'col3'       table1 join           (select @pv:='1') tmp           on find_in_set(col1, @pv) > 0      ) t 

something should work small data sets. however, idea of putting ids in string limited capacity of string.