Mysql – how to trace the data whether the destination stop using thesql

MySQL

I am newbie in programming.. I have table test and 3 columns(source, from and destination) below..

Table test

  source     from                destination     
  01         VALID/NUM-6283      Local/00000042;1
  02         Local/00000042;2    Local/00000043;1
  03         Local/00000043;2
  04         NOT_VALID/NUM-0000  Local/00000000;0
  05         Local/000000d2;2
  06         Local/0000002b;2    Local/000000d2;1    
  07         VALID/NUM-1234      Local/0000002b;1

working fiddle here

the first row (source 01) has :

  from                destination  
  VALID/NUM-6283      Local/00000042;1

the 2nd row (source 02) has :

  from                destination  
  Local/00000042;2    Local/00000043;1

the 3rd row (source 03) has :

  from                destination  
  Local/00000043;2

the destination on 3rd row is empty..as you can see the the first row. the column "destination" has Local/00000042;1;

then the second row; on the column "from" has Local/00000042;2;

there last character is "42;1" and "42;2";

then it continues til "43;1" in "source02->destination" and 43;2 in "source03->from"

is there any way to do that using mysql…

i want to focus on the data on column "from" that has only a prefix VALID! e.g. (VALID/NUM-6283)

then trace where on column "destination" where it ends …

  VALID/NUM-6283   ->  VALID/NUM-6283  ->  Local/00000042;1  -> 
  Local/00000042;2  ->  Local/00000043;1  ->  Local/00000043;2  -> 
  ends on "destination" in "source03"

hope its make sense…. can anyone tell me how to do it optimizely?? because what if the table consist of a thousands of data….

Best Answer

If I am not mistaken you are looking for a way to traverse your table as a tree and trace an item to see where it ended. I don't like your table desing and also what you are doing but I have a possible solution for it.

I cannot think of a simple query to do it but programmatically its possible. I have written a procedure to handle it. Please note that this procedure assumes that final destination is inserted into the table after start point and also this might not work in all your cases since I do not now what are the other possible input patterns. Try this. Hope it helps

drop procedure if exists traceMe;
delimiter $$
CREATE PROCEDURE traceMe(in input varchar(80))
BEGIN
  DECLARE bDone INT;
  DECLARE src,frm,des,startPoin varchar(80);    -- or approriate type

  DECLARE curs CURSOR FOR  SELECT * FROM table_test;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = 1;

  OPEN curs;
SELECT Destination into startPoin from table_test where `from` = input;
  SET bDone = 0;
  REPEAT
    FETCH curs INTO src,frm,des;
     -- select CONCAT(LEFT(startPoin,LENGTH(startPoin) -1),'2');
     -- select frm;
    IF (frm = CONCAT(LEFT(startPoin,LENGTH(startPoin) -1),'2')) Then
      Set startPoin = des;
    END IF;
  UNTIL bDone END REPEAT;

  CLOSE curs;
  SELECT startPoin;
END$$

delimiter ;

call traceMe('VALID/NUM-6283');