I have a pretty complex table structure with parent-child relations. The idea behind the structure is that some object in child_id can trigger a parent_id. Assume this data;
Table 1 - map
map_id | parent_id | child_id
1 | 1 | 2
2 | 1 | 3
3 | 1 | 4
Table 2 - attributes
attribute_id | child_id | id_to_trigger
1 | 2 | 5
2 | 5 | 6
Example: A questionnaire system is a master. It can contain sub groups to be answered; in which case the sub groups become child of the master. Some answers in the sub groups can trigger an additional sub group within it.
I want to now be able to fetch all the sub group id's for a given master. A sub group can be triggered from multiple sub groups but that isn't a problem since I need just the sub group id's.
As you can tell, master with id 1 has 3 sub groups 2, 3, 4. In the attributes table we can see that sub group 2 can trigger sub group 5; similarly 5 can trigger 6 and so on.
I need 2, 3, 4, 5, 6 in my output. How do i achieve this?
Think about your design, i suggest that you dont need 2 tables if you add these 2 recs to your table 1
map_id | parent_id | child_id
1 | 1 | 2
2 | 1 | 3
3 | 1 | 4
4 | 2 | 5
5 | 5 | 6
you can now use a standard CTE to walk the tree
like this
with Tree as (select child_id from table_1 where parent_id = 1
union all
select table_1.child_id from table_1
inner join Tree on Tree.child_id = table_1.parent_id)
select * from Tree
if you cant change schema this will work
with
table_1 as ( select Parent_id , child_id from map
union all
select child_id as Parent_id, id_to_trigger as child_id from attributes)
,Tree as (select child_id from table_1 where parent_id = 1
union all
select table_1.child_id from table_1
inner join Tree on Tree.child_id = table_1.parent_id)
select * from Tree