I have a database with a table which looks somewhat like this :
Root | Connector
A | B
B | C
C | D
D | E
E | -
So i want to retrieve the chain for any given root till the connector is blank
For example : Chain of A means a->B->C->D->E whereas Chain of C means C->D->E
I am using mysql database.
Thanks in advance
I just try with loop structure in Mysql and got success. Posting to share only--
CREATE PROCEDURE `root_connect`(IN init char(1),OUT str char(15))
BEGIN
set @startChar:=(select connector from tableName where root = init);
set @endloop := "no";
set @fullchar:= @startChar;
set @newchar:= "";
if (@startChar !="-" OR @startChar =null) then
WHILE (@endloop = "no") DO
set @newchar :=(select connector from tableName where root = @startChar);
if(@newchar = '-') THEN
set @endloop := "yes";
else
set @fullchar:= concat(@fullchar,"-",@newchar);
end if;
set @startChar := @newchar;
END WHILE;
end if;
select @fullchar;
END
Since MySQL does not allow the users to create a recursive function, let me show you using stored procedure:
Assuming the table you are working on is named "test".
DELIMITER $$
DROP PROCEDURE IF EXISTS build_chain$$
CREATE PROCEDURE build_chain(init CHAR(1))
BEGIN
IF init != '-' THEN
SET @r := (SELECT DISTINCT(root) FROM test WHERE root = init);
SET @search_type := TRUE;
SET @result := @r;
END IF;
SET @r := (SELECT DISTINCT(connector) FROM test WHERE root = @r AND connector != '-');
SET @result = CONCAT_WS('->', @result, @r);
SET @search_type = IF(@search_type, FALSE, TRUE);
IF @r IS NOT NULL THEN CALL build_chain('-'); ELSE SELECT @result AS result_chain; END IF;
END$$
DELIMITER ;
Usage:
SET max_sp_recursion_depth = 255;
CALL build_chain('a');
Result (the chain) is stored in @result variable.
Note: you can also build the chain using programming language other than MySQL.
