我想要的是，对于每个产品，根据产品的部门id获取该产品的url

select id, title, parent_id from departments where id = prod.department_id

我得到了错误 ERROR 1054 (42S22): Unknown column 'prod.department_id' in 'where clause' .
如果我用一个常量（如4）替换此行中的prod.department\u id，整个过程都会正常工作，只是每个产品的url都相同。
我无法访问子查询中每个产品的列，我该怎么做？我在os x上使用mysql 8.0.12，下面是完整的查询：

select id, title, short_description,
(base_price+base_price*tax) as retail_price,

lower(concat('www.someurl.com/',
(with recursive cte (id, title, parent_id) as (
  select id, title, parent_id from departments where id = prod.department_id
  union all (
    select p.id, p.title, p.parent_id
    from departments p inner join cte on cte.parent_id = p.id
  )
) select
GROUP_CONCAT(lower(title) order by id SEPARATOR '/') as path from cte
))) as url

from products prod
inner join featured_products on featured_products.product_id = prod.id;