Use column from outer query in inner query (correlated subquery?)


What I want is for each product, get the url for that product based on the product's department_id. The recursive query works, the line that is the problem is basically

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

I get the error ERROR 1054 (42S22): Unknown column 'prod.department_id' in 'where clause'.

If I replace prod.department_id in this line with a constant like 4, the whole thing works, only I get the same url for every product.

I can't access each products' columns inside the subquery, how do I do this? I'm using mysql 8.0.12 on OS X, here is the full query:

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;
- - Source
comments powered by Disqus