@ 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?

Zymometer

A problem with this seems to be that it relies on the records being returned in a correct order. For example if I take your test data and insert them into a table, but insert them in a reverse order your SQL only returns the first row (where col1 = 1). Other records have been processed and discarded before their parent has been found. To get round this you would need to add an order clause, and no obvious way to do that for your data. The solution by @GordonLinoff below suffers similarly. – Curie

Yes, that is the second prblem – Zymometer

Perhaps, those who said that there's no solution for a recursive SELECT had the second problem in mind. (Meaning, there's probably no actual, reliable solution after all.) – Anticipative

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.

Wingspan

Thax u Gordon, it 's almost there, like Kicktart said ur query does not work if it in the reversed order. do u figure out how to solve the order problem? – Zymometer

The string may not be an isue since there only 20-30 links? DO u think so? – Zymometer

Answer 2

In my limited deep of hierarchy-levels, I used the following:

parents:

select * from mytable
join (
    select A.id Aid,B.id Bid, C.id Cid, D.id Did, E.id Eid, F.id Fid,G.id Gid, H.id Hid from mytable A
    left join mytable B on B.id=A.parent
    left join mytable C on C.id=B.parent
    left join mytable D on D.id=C.parent
    left join mytable E on E.id=D.parent
    left join mytable F on F.id=E.parent
    left join mytable G on G.id=F.parent
    left join mytable H on H.id=G.parent
    where A.id=9
) X
where id in (Aid,Bid,Cid,Did,Eid,Fid,Gid,Hid);

children:

select * from mytable where id in (
select distinct id from mytable
join (
    select A.id Aid,B.id Bid, C.id Cid, D.id Did, E.id Eid, F.id Fid,G.id Gid, H.id Hid FROM mytable A
    left join mytable B on B.parent=A.id
    left join mytable C on C.parent=B.id
    left join mytable D on D.parent=C.id
    left join mytable E on E.parent=D.id
    left join mytable F on F.parent=E.id
    left join mytable G on G.parent=F.id
    left join mytable H on H.parent=G.id
    Where A.id=1
) X
where id in (Aid,Bid,Cid,Did,Eid,Fid,Gid,Hid)

);

Tarrant

Answer 3

Had more of a play. Can't get it to work using the user variables due to the ordering of items.

However if you have a reasonable maximum number of levels then you can do something like this:-

SELECT CONCAT_WS('-', a.allCols, b.allCols, c.allCols, d.allCols, e.allCols, f.allCols, g.allCols, h.allCols, i.allCols, j.allCols, k.allCols, l.allCols, m.allCols)
FROM (SELECT col1, col3, CONCAT(col1, col2, col3) AS allCols FROM table1) a
LEFT OUTER JOIN(SELECT col1, col3, CONCAT(col1, col2, col3) AS allCols FROM table1) b ON a.col3 = b.col1
LEFT OUTER JOIN(SELECT col1, col3, CONCAT(col1, col2, col3) AS allCols FROM table1) c ON b.col3 = c.col1
LEFT OUTER JOIN(SELECT col1, col3, CONCAT(col1, col2, col3) AS allCols FROM table1) d ON c.col3 = d.col1
LEFT OUTER JOIN(SELECT col1, col3, CONCAT(col1, col2, col3) AS allCols FROM table1) e ON d.col3 = e.col1
LEFT OUTER JOIN(SELECT col1, col3, CONCAT(col1, col2, col3) AS allCols FROM table1) f ON e.col3 = f.col1
LEFT OUTER JOIN(SELECT col1, col3, CONCAT(col1, col2, col3) AS allCols FROM table1) g ON f.col3 = g.col1
LEFT OUTER JOIN(SELECT col1, col3, CONCAT(col1, col2, col3) AS allCols FROM table1) h ON g.col3 = h.col1
LEFT OUTER JOIN(SELECT col1, col3, CONCAT(col1, col2, col3) AS allCols FROM table1) i ON h.col3 = i.col1
LEFT OUTER JOIN(SELECT col1, col3, CONCAT(col1, col2, col3) AS allCols FROM table1) j ON i.col3 = j.col1
LEFT OUTER JOIN(SELECT col1, col3, CONCAT(col1, col2, col3) AS allCols FROM table1) k ON j.col3 = k.col1
LEFT OUTER JOIN(SELECT col1, col3, CONCAT(col1, col2, col3) AS allCols FROM table1) l ON k.col3 = l.col1
LEFT OUTER JOIN(SELECT col1, col3, CONCAT(col1, col2, col3) AS allCols FROM table1) m ON l.col3 = m.col1
WHERE a.col1 = 1

This is coping with up to 13 levels (OK, only a couple used in your test data), and will give a comma separated bit for each column, with each row joined with a dash (-).

Curie

But the number of the links is unknown, ie it could have only 3 links or 5 links or 20 links – Zymometer

Doesn't matter, as long as you have at least as many joins. You say there are only 20~30 links, so that should be easily covered with this. – Curie

I sticked with Gordon's solution. Thank you anyway. – Zymometer

Fair enough if the insert ordering is not an issue. – Curie

Answer 4

Stored procedure is the best way to do it. Because Gordon's solution would work only if the data follows the same order.

If we have a table structure like this

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

It wont work.

Here is a sample procedure code to achieve the same.

delimiter //
CREATE PROCEDURE chainReaction 
(
    in inputNo int
) 
BEGIN 
    declare final_id int default NULL;
    SELECT col3 into final_id from table1
    where col1 = inputNo;
    if( final_id is not null) then
        insert into results(select col1, col2, col3 from table1 where col1 = inputNo);
        CALL chainReaction(final_id);   
    end if;
END//
delimiter ;

call chainReaction(1);
select * from results;
drop table if exists results;

Pigling