1

SO I have a table team

team(id, name, parentId).

I have table team as follows:

id      name               parentId
 1      root                null
 2      child1              1
 3      child2              2

SO, I have teamId= 1 I can get the team with teamId 1 along with subteams of teamId 1 with the help of parentId.But is it possible to get the child2 because its the subchild of child1 which is the child of root i.e. teamId 1, with just the teamId information ? Or to make it easier does the table need to be created differently like Teams, Subteams and TeamSubteam with many to many relation ?

Team.find({
            where: {
                or: [{
                    id: teamId
                }, {
                    parentId: teamId
                }]
            },
            fields: ['id']
        }, function (err, teams) { });
whats in a name
  • 87
  • 6
  • Tag your question with a specific DB you are using. A recursive query like: https://stackoverflow.com/questions/1757370/recursive-same-table-query-in-sql-server-2008/1758797#1758797 – Damir Sudarevic May 05 '20 at 11:56
  • You may consider using a closure table as explained here - https://stackoverflow.com/a/38701519/5962802 – IVO GELOV May 05 '20 at 12:00

1 Answers1

0

This is a simple recursive query:

with recursive all_teams as ( 
  select *
  from team 
  where teamid = 1 -- or wherever you want to start
  union all
  select sub.*
  from team sub
   join all_teams p on p.id = sub.parentid
)
select *
from all_teams;