我有一个特定的mysql（版本5）查询：

SELECT finding.ID, finding.Material,
(SELECT CASE
            WHEN 'Metal' IN (
                SELECT m2.Value FROM (SELECT @r AS _id, (SELECT @r := ParentID FROM material WHERE ID = _id) AS ParentID, @l := @l + 1 AS lvl 
                FROM (SELECT @r := finding.Material, @l := 0) vars, material m WHERE @r <> 0) m1 JOIN material m2 ON m1._id = m2.ID) 
            THEN 'Met' 
            ELSE '' 
        END AS result) 
AS 'finding.MaterialGroup',finding.Description FROM finding;

查询从“查找”表中搜索数据。该表包含“材料”列。此列引用另一个表，该表是根据子-父原则构造的。
这个复杂的子查询的目的是列出Material列的所有父项，以便检查其中的特定值。
问题：我得到错误“Unknown table 'finding' in field list”。所以我不能使用代码：“@r：=查找.Material”。在子查询中，可能无法识别查找表，因为它不在子查询的范围内。
如果我试着遵循代码，它就能工作。

SELECT finding.ID, finding.Material,
(SELECT CASE
            WHEN 'Metal' IN (
                SELECT m2.Value FROM (SELECT @r AS _id, (SELECT @r := ParentID FROM material WHERE ID = _id) AS ParentID, @l := @l + 1 AS lvl 
                FROM (SELECT @r := 3, @l := 0) vars, material m WHERE @r <> 0) m1 JOIN material m2 ON m1._id = m2.ID) 
            THEN 'Met' 
            ELSE '' 
        END AS result) 
AS 'finding.MaterialGroup',finding.Description FROM finding;

现在有人有解决办法吗？
下面是设置示例表的代码，如果你想自己尝试的话：

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`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=UTF8;

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) NOT NULL DEFAULT '',
  `ParentID` int(10) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=UTF8;

INSERT INTO material
(id, `value`, ParentID)
VALUES
(1, 'Metal', 0),
(2, 'Tree', 1),
(3, 'Apple', 2),
(4, 'Table', 0);

我希望结果是这样的：