1

Regarding company tree in PostgreSQL

I am using the following to get the subsidiaries of the company with id = 11.

SELECT * FROM "OwnershipTable" 
WHERE "Parent_ID" = 11;

giving me the following output

Company_ID Company_Name Parent_ID Parent_Name
111 Holdco 1 11 Topco
112 Holdco 2 11 Topco
113 Holdco 3 11 Topco
114 Holdco 4 11 Topco

However, I would like to investigate if any of the Holdco-companies has any subsidiaries. My question is therefore: Is it possible insert the column "Company_ID" as "Parent_ID" in the query using some sort of loop?

Frederik Grut
  • 11
  • 1

2 Answers2

0

Yes. This is called a recursive CTE:

with recursive cte as (
      select company_id as parent_company_id, company_id as child_id
      from OwnershipTable ot
      where parent_id = 11
      union all
      select cte.parent_company_id, ot.company_id
      from cte join
           OwnershipTable ot
           on ot.parent_id = cte.child_id
     )
select *
from cte;

If you want additional information about the companies, you can join it in or include it in the recursive CTE definitions.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks, very helpful. However, it is possible for the "children" to have children and so on. To your knowledge is it possible to use a recursive CTE n-times until there is no "children" left? – Frederik Grut Apr 27 '21 at 13:48
  • @FrederikGrut . . . That is what this query does. That is why it is using a recursive CTE. Perhaps you intended the comment on the other answer. – Gordon Linoff Apr 27 '21 at 15:06
0

This should work:

  SELECT * FROM "OwnershipTable" ot
    WHERE EXISTS(SELECT 1 FROM "OwnershipTable" where Parent_ID = ot.Company_ID)
Piotr Cendro
  • 1
  • 1