By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE IF NOT EXISTS `finding` ( `ID` int(10) NOT NULL AUTO_INCREMENT,
`Description` text, `Material` int(11) NOT NULL DEFAULT '-1',
PRIMARY KEY (`ID`));
INSERT INTO finding(id, `description`, material)
VALUES(1, 'Test1', 1),(2, 'Test2', 2),(3, 'Test3', 4);
CREATE TABLE IF NOT EXISTS `material` ( `ID` int(10) NOT NULL,
`Value` varchar(50) , `ParentID` int(10) NOT NULL, PRIMARY KEY (`ID`));
INSERT INTO material(id, `value`, ParentID)
VALUES(1, 'Metal', 0),(2, 'Tree', 1),(3, 'Apple', 2),(4, 'Table', 0);
Records: 3 Duplicates: 0 Warnings: 0
Records: 4 Duplicates: 0 Warnings: 0
create procedure get_metal (i_id int, out ret int)
begin
declare v_value varchar(50);
declare v_parent_id int;
set max_sp_recursion_depth=50;
select value, parentid into v_value, v_parent_id from material where id = i_id;
if (v_value = 'Metal') then
set ret = 1;
elseif v_parent_id = 0 then
set ret = 0;
else
call get_metal(v_parent_id, ret);
end if;
end;
CREATE FUNCTION find_metal(i_id int) RETURNS int
BEGIN
DECLARE res int;
CALL get_metal(i_id, res);
RETURN res;
END;
select find_metal(4)
| find_metal(4) |
|---|
| 0 |
SELECT ID, Material, case when find_metal(material) = 1 then 'Met' END AS result
FROM finding;
| ID | Material | result |
|---|---|---|
| 1 | 1 | Met |
| 2 | 2 | Met |
| 3 | 4 | null |