MySQL doesn't provide a way to do connect by , so how do you tackle it ? You have to write your own function or procedure that will do the connect by . Here is an example of the skill table that is related to itself by s_parentskillid .
Here is the Code that will get the skills related till it gets to the parent .
Code:
DROP PROCEDURE IF EXISTS getskillpath;
CREATE PROCEDURE `getskillpath`(in skillid int ,in delimiter varchar(5), out v_skillpath varchar(500))
BEGIN
DECLARE v_skill VARCHAR(50); /* variable stores the name */
DECLARE v_skillid INT DEFAULT 0; /* skill id - primary key */
DECLARE v_parentskillid INT DEFAULT 0;
/* parent skill id - foreign key that maps to skill id */
SET v_skillid = skillid; /* assign first skillid to variable */
skill_loop: LOOP
SELECT s_parentskillid,
s_name
INTO
v_parentskillid,
v_skill
FROM skills
where s_id =v_skillid;
SET v_skillpath = CONCAT_WS ( delimiter,v_skill,v_skillpath );
/* set skillpath initially null */
IF v_parentskillid is null THEN
LEAVE skill_loop; /* condition to exit parent found */
else
SET v_skillid = v_parentskillid; /* reset the skill id to loop */
END IF;
END LOOP skill_loop;
SELECT v_skillpath; /* check output */
END;
Example output - sap>SAP Functional>Reports>Reports Sub . if I use 9 as the input skill and > as the delimiter .( rows affected are marked in the image ) .
