我有以下数据集，基本上是所有节点所属的树：

name         |id   |parent_id
Planet Earth |1    |0
North America|2    |1
Costa Rica   |46408|2
Alajuela     |46409|46408
Cartago      |46410|46408
Guanacaste   |46411|46408
Heredia      |46412|46408
...

我希望能够通过查询获得树中所有的底部节点，下面的查询非常适合这一点：

SELECT * 
FROM locations l 
WHERE l.id NOT IN (SELECT parent_id FROM locations)
AND l.name LIKE ?

我想以以下格式输出数据：
查询

SELECT * 
FROM locations l 
WHERE l.id NOT IN (SELECT parent_id FROM locations)
AND l.name LIKE 'he%'
SPOOKY SQL MAGIC GOES HERE...

输出

name   |id   |parent_id|parents
Heredia|46412|46408    |Planet Earth > North America > Costa Rica

我自己也开始做这个查询，比如：

WITH CTE AS (
    SELECT id, parent_id, name, CONVERT(VARCHAR(MAX),name) AS parents
        FROM locations
        WHERE idParent = 0
    UNION ALL
        SELECT l.id, l.parent_id, l.name,  c.parents + ' > ' + CONVERT(VARCHAR(MAX),t.name) AS parents
        FROM locations l
        INNER JOIN CTE c ON l.idParent = c.id
)
SELECT * FROM CTE

如您所见，我将递归用于常见的表表达式，但我不知道如何组合这两个查询。上面的查询也在整个表上运行。
如何将上述cte查询限制为表的一个子集？
我如何实现我想要的输出？