it has been a time for me since doing SQL, so I just want to ask for help with this one.

I would like a solution without temp table if possible, but better than nothing ofc, would be glad for every solution! Not really sure if this is possible without it...

I have two tables.

Table Premise

Table Category - this is just slice coresponding to the above premise_id 3. I need for every premise to get it's category AND all of it's parents.

The RESULT should look like this:

All I managed to write is this recursive query for getting the tree inside category:

with recursive categories as (
    select id, id_path, parent_id, name from category
    union all
    select c.id, c.id_path, c.parent_id, c.name from category c 
    join categories ctgs on ctgs.parent_id = c.id
) select * from categories
;

Getting lazy, will do prtscreens now. It results fine like this. There IS the parents-path itself, as id_path, but would not like to rely on that. Worth noting is that not always the children has higher ID than parent !

---------- But I am not really sure how to join it. Feels like I am missing something really obvious, but really, what is the join condition on this? Or should I go with temp table? I am using Mariadb 10.5.

Thank you for your time guys.

CodePudding user response：

You want your recursive query to produce rows with category id and ancestor category id (so multiple rows for each category id); the path isn't as easily joinable, and the id/parent id aren't enough to join with a single id and get all the ancestors.

So something like:

with recursive category_ancestors as (
    select id as category_id, id as ancestor_id from category
    union all
    select category_id, parent_id
    from category_ancestors
    join category on category.id=ancestor_id
    where parent_id is not null
)
select premise_id, ancestor_id
from premise
join category_ancestors using (category_id)

(obviously joining again to category to get the name if you need that)