SQL - joining on recursive table

Question

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

premise_id	category_id
1	5
2	8
3	20
4	6

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.

id	parent_id	name
20	17	nameA
17	11	nameB
11	5	nameC
5	null	nameD

The RESULT should look like this:

premise_id	category_id	category_name
3	20	nameA
3	17	nameB
3	11	nameC
3	5	nameD

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.

ysth · Accepted Answer · 2022-10-26 16:58:42Z

1

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)

answered Oct 26, 2022 at 16:58

ysth

99.1k6 gold badges126 silver badges219 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jan Janáček Over a year ago

Thank you man, this works, solution is perfect for me. Just for clearance I am posting the SQL with the join. ```

Jan Janáček Over a year ago

Nope, i am not - just thanx ))

Collectives™ on Stack Overflow

SQL - joining on recursive table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related