title: @ Symbol - a solution for Recursive SELECT query in Mysql?
body: <p>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".</p>
  
  <p>Actually there is a certain solution &amp; I want to know it clearly, so this question is the following of the previous question that can be found at (<a href="https://stackoverflow.com/questions/16513418/how-to-do-the-recursive-select-query-in-mysql">how-to-do-the-recursive-select-query-in-mysql</a>)</p>
  
  <p>Suppose you have this table:</p>
  
  <pre><code>col1 - col2 - col3
  1    -  a   -  5
  5    -  d   -  3
  3    -  k   -  7
  6    -  o   -  2
  2    -  0   -  8
  </code></pre>
  
  <p>&amp; you want to find all the links that connect to value "1" in col1, i.e. you want to print out: </p>
  
  <pre><code>1 - a - 5
  5 - d - 3
  3 - k - 7
  </code></pre>
  
  <p>Then you can use this simple query:</p>
  
  <pre><code>select col1, col2, @pv:=col3 as 'col3' from table1
  join
  (select @pv:=1)tmp
  where col1=@pv
  </code></pre>
  
  <p>Ok, good, however, if your table has 2 records containing "1" in col1 &amp; 2 records containing "3" in col1, ex:  </p>
  
  <pre><code>col1 - col2 - col3
  1    -  a   -  5
  1    -  m   -  9
  5    -  d   -  3
  3    -  k   -  7
  6    -  o   -  2
  3    -  v   -  10
  2    -  0   -  8
  </code></pre>
  
  <p>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:  </p>
  
  <pre><code>col1 - col2 - col3
  1    -  a   -  5
  1    -  m   -  9
  5    -  d   -  3
  3    -  k   -  7
  3    -  v   -  10
  </code></pre>
  
  <p>So, my question is <strong>how do we modify the above query so that it will show all the links as in the above expecting result?</strong></p>
  
  <p><strong>EDIT:</strong>  @ Gordon,
  but if we omit <code>select distinct col1, col2 from</code> then this query means something, can you work on this (since the childID got increased, so we can order the table1 ):</p>
  
  <pre><code>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) &gt; 0
  </code></pre>
  
  <p>In this case, we don't worry about the order, for example, if this is the data:  </p>
  
  <pre><code>col1 - col2 - col3
  4    -  a   -  5
  1    -  d   -  2
  1    -  k   -  4
  2    -  o   -  3
  6    -  k   -  8
  8    -  o   -  9
  </code></pre>
  
  <p>the output will be:</p>
  
  <pre><code>col1 - col2 - col3
  1    -  d   -  1,2
  1    -  k   -  1,2,4
  2    -  o   -  1,2,4,3
  </code></pre>
  
  <p>So we get this result <code>1,2,4,3</code> right? &amp; we just select all records if the col1 is in <code>1,2,4,3</code>. Then we can get the final expected result. </p>
  
  <p>If that is the case, can you think of any special case that rules out the solution I just mentioned?</p>