SQL - How to check if users are in the same hierarchy?

userksdm1 0
Viewed 24

I want to find out if users are directly in a parent child relation.

Given my user table schema

User_id | Parent_ID | Name

For example, I have a list of user_id's and I want to know if they are all in the same hierarchical tree.

I have tried using CTE recursive.

Sample data

User_id | Parent_ID | Name
1       |           |   A
2       |     1     |   B
3       |     2     |   C
4       |     3     |   D
5       |     2     |   E
6       |     1     |   F

Desired result: Input [2,3,4] => Same Team

Input [2,3,6] => Not same team

hierarchical-data sql postgresql
1 Answers

Use the top-level parents' parent_id as the hierarchy identifier:

with recursive hierarchies as (
  select user_id, user_id as hierarchy_id
    from ttable
   where parent_id is null
  union all
  select c.user_id, p.hierarchy_id
    from hierarchies p
         join ttable c on c.parent_id = p.user_id
)
select * from hierarchies;

With that mapping of each user_id to a single hierarchy_id, you can join to your list of users.

EDIT BEGINS

Since you added sample data and example results that do not match your original question, here is an example of how any minimally competent programmer could slightly tweak the above to match the newly added contradictory examples:

with recursive subhierarchies as (
  select user_id, array[user_id] as path
    from ttable
   where parent_id is null
  union all
  select c.user_id, p.path||c.user_id as path
    from subhierarchies p
         join ttable c on c.parent_id = p.user_id
)
select d.user_ids, count(s.path) > 0 as same_team
  from (values (array[2, 3, 4]), (array[2, 3, 6])) as d(user_ids)
       left join subhierarchies s
         on s.path @> d.user_ids
 group by d.user_ids
;
Mike Organek0
Related