CODEWITHSUNDEEP
sqloracle-databasetree
ThZ
ThZ

Reputation: 137

Getting all the children of a specific individual (tree-based hierachy)

I am currently trying to get every relation & childs of any specific place of a tree. The tables are the following :

TABLE ORGANISATION                      TABLE LINKS
| Orga_Name | Code  | DELETED |         | Code  | Dads_Code |  STT  |
| DR        | DR001 |  FALSE  |(root)   | DR001 |   Null    |   OK  |(root no dad snif)
| DSI       | DS001 |  FALSE  |         | DS001 |   DR001   |   OK  |
| DLE       | DL001 |  FALSE  |         | DL001 |   DR001   |   OK  |
| DMP       | DM001 |  FALSE  |         | DM001 |   DS001   |   OK  |
| TRS       | TR001 |  FALSE  |         | TR001 |   DM001   |   OK  |
| TRE       | TE001 |  TRUE   |         | TE001 |   DM001   |   NOK |
| TRC       | TC001 |  FALSE  |         | TE001 |   DM001   |   NOK |

If any row has either DELETED = TRUE or STT = NOK, it shouldnt be considered at all. (just like TRE and TRC in this example)

The perfect result would be like the following:

|  Code  |   PICK-ONE|  Path                     |
|  DS001 |   DR001   |  DS001/DR001              |
|  DL001 |   DR001   |  DL001/DR001              |
|  DM001 |   DR001   |  DM001/DS001/DR001        |
|  TR001 |   DR001   |  TR001/DM001/DS001/DR001  |
|  DM001 |   DS001   |  DM001/DS001              |
|  TR001 |   DS001   |  TR001/DM001/DS001        |
|  TR001 |   DM001   |  DM001/TR001              |

So I can select a code in 'PICK-ONE' and get all the (direct & indirect) children of it as a list. I have been trying to use CONNECT_BY_ROOT, here's the current code I am using :

    SELECT  Code, 
            CONNECT_BY_ROOT Code,
            LEVEL-1, 
            SYS_CONNECT_BY_PATH(Code, '/') "Path"--,STT

    FROM    (
            SELECT o1.*,a1.STT,o1.DELETED
            FROM ORGANISATION o1
            LEFT JOIN LINKS a1
            ON o1.Code=a1.Code
            AND DELETED = 'FALSE'
            )
    WHERE LEVEL > 0 AND STT like 'OK' 
    CONNECT BY PRIOR Code = Dads_Code;

I am getting results mostly from the root, and one of his (always the same) children. In my example, the result I get would be from DS001 and DR001. Some of the results are correct. sadly the analyze stops at 2 level maximum,there are some duplicates, and also some path to itself results (with 0 as level).

I have been trying to change Level or path, or change some conditions, but I am unable to find the problem. I would be glad if you could help me!

Upvotes: 0

Views: 27

Answers (1)

The Impaler
The Impaler

Reputation: 48770

Don't use CONNECT BY since it's mostly obsolete at this point. It was a good way of querying hierarchies back in the day where there wasn't anything standard.

Use a Recursive Common Table Expression (aka Recursive CTE) available in most databases as part of the SQL standard.

For example if you want to retrieve all children of DR001, you can do:

with n (code, dads_code, lvl, path) as (
  select code, dads_code, 1, code from links where code = 'DR001'
  union all
  select l.code, l.dads_code, n.lvl + 1, n.path || '/' || l.code
    from links l
    join n on n.code = l.dads_code
    where l.stt <> 'NOK'
      and not exists (select 1 from organisation o 
                      where o.code = l.code and deleted = 'TRUE')
)
select code, dads_code, lvl, path from n

Upvotes: 1

Related Questions