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

Question

there are a lot of questions about Recursive SELECT query in Mysql, but most of answers is that "There NO solution for Recursive SELECT query in Mysql".

Actually there is a certain solution & I want to know it clearly, so this question is the following of the previous question that can be found at (how-to-do-the-recursive-select-query-in-mysql)

Suppose you have this table:

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

& you want to find all the links that connect to value "1" in col1, i.e. you want to print out:

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

Then you can use this simple query:

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

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

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

Then, when users search for "1" in col1, it should show all the links connecting to 2 "1", i.e. it should show this expecting result:

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

So, my question is how do we modify the above query so that it will show all the links as in the above expecting result?

EDIT: @ Gordon, but if we omit select distinct col1, col2 from then this query means something, can you work on this (since the childID got increased, so we can order the table1 ):

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

In this case, we don't worry about the order, for example, if this is the data:

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

the output will be:

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

So we get this result 1,2,4,3 right? & we just select all records if the col1 is in 1,2,4,3. Then we can get the final expected result.

If that is the case, can you think of any special case that rules out the solution I just mentioned?

Answer 1

I keep wondering if something like this would work:

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

Something like this should work for small data sets. However, the idea of putting all the ids in a string is limited to the capacity of a string.