hierarchical records using sql

Question

I have a table like this

**id    exType               exId   depType              depId**    
  1     Ceo                  1      Board Of Director      1    
  2     Board Of Director    1      Manager                4    
  3     Board Of Director    1      Manager                5    
  4     Manager              4      TeamLead               1    
  5     Manager              5      TeamLead               2    
  6     TeamLead             1      Member                 7    
  7     TeamLead             1      Member                 8    
  8     TeamLead             1      Member                 9    
  9     TeamLead             1      Member                 10   
  10    TeamLead             2      Member                19

I want all the member's when i give input as exType as Manager and exId as 4 i want

                                    Member                 7    
                                    Member                 8    
                                    Member                 9    
                                    Member                 10

or I want all the member's when i give input as exType as teamlead and exId as 1 i want

                                    Member                 7    
                                    Member                 8    
                                    Member                 9    
                                    Member                 10

or or I want all the member's when i give input as exType as CEO and exId as 1 i want

                                    Member                 7    
                                    Member                 8    
                                    Member                 9    
                                    Member                 10

. . I removed the inconsistent database tags. Please tag with the database you are really using. — Gordon Linoff, Apr 30 '20 at 18:01

score 1 · Answer 1 · answered Apr 30 '20 at 18:14

1

You can use a recursive query to exhibit all dependents of a given node:

with recursive cte as (
    select depType, depId 
    from mytable 
    where exType = 'Manager' and exId = 4
    union all
    select t.depType, t.depId
    from cte c
    inner join mytable t on t.exId = c.depId and t.exType = c.depType
)
select * from cte

answered Apr 30 '20 at 18:14

GMB

216,147
25
84
135

@David: the syntax for recursive queries sligthly varies across databases (and it might depend on the database version too). In which database/version do you want to run this? – GMB May 04 '20 at 16:47
currently its for Oracle (11)and MySql(5.7) databases – David May 05 '20 at 09:12
@David: mysql supports recursive queries starting version 8.0 only. In oracle, small changes are needed. You can't really have a recursive query syntax that woks in all databases. You tagged your question `postgres`, so I provided a solution for this particular database. – GMB May 05 '20 at 16:26
thank u, it would be helpful if u could provide for both databases (Mysql/Oracle) – David May 06 '20 at 06:10

hierarchical records using sql

1 Answers1