Hierarchical SQL Question

with src (id, parentid, val) as (
  select 1, cast(null as int), 5 from dual union all
  select 2, 1, 9 from dual union all
  select 3, 2, null from dual union all
  select 4, 2, null from dual union all
  select 5, null, 10 from dual union all
  select 6, 5, 7 from dual
  
)
select
  connect_by_root id as tree_id
  , id
  , parentid
  , val
  , max(decode(connect_by_isleaf, 1, val))
      over(partition by connect_by_root id) as val_root
from src
start with id in (3, 4, 6)
connect by id = prior parentid

order by 1, 2, 3

TREE_ID	ID	PARENTID	VAL	VAL_ROOT
3	1	-	5	5
3	2	1	9	5
3	3	2	-	5
4	1	-	5	5
4	2	1	9	5
4	4	2	-	5
6	5	-	10	10
6	6	5	7	10

Hierarchical query with some joins

You can use a hierarchical query joined to the other two tables:

SELECT DISTINCT
       t1.uuid,
       t1.miscdata,
       t1.keytolookup,
       t1.conditionclause,
       t2.tariffdomainid,
       t2.depth
FROM   tbl1 t1
       LEFT OUTER JOIN tbl3 t3
       ON ( t1.keytolookup = t3.firsttblkey )
       OUTER APPLY (
         SELECT tariffdomainid,
                LEVEL AS depth
         FROM   tbl2 t2
         WHERE  t2.tariffdomainid = t3.secondtblkey
         START WITH
                t2.feetype = t1.conditionclause
         CONNECT BY
                PRIOR TariffDomainID = UpperTariffDomainID
       ) t2
ORDER BY
       uuid,
       depth

Which, for the sample data, outputs:

UUID | MISCDATA        | KEYTOLOOKUP | CONDITIONCLAUSE     | TARIFFDOMAINID | DEPTH
---: | :-------------- | ----------: | :------------------ | -------------: | ----:
  13 | ATM             |       12345 | null                |           null |  null
 447 | Balance Inquiry |       67890 | BALANCE_INQUIRY_FEE |           1543 |     1
 447 | Balance Inquiry |       67890 | BALANCE_INQUIRY_FEE |           1543 |     3
 789 | Credit          |       22321 | CREDIT_FEE          |           1543 |     2

(Note: you need the DISTINCT as there are multiple 1543 and 154 entries in TBL2 so the hierarchical query can take multiple paths to get from the start to the end condition. If your actual data does not have these duplicates then you should be able to remove the DISTINCT clause.)

db<>fiddle here

SQL query for hierarchical number

You want a recursive CTE

with h as (
  select *, cast(OrderNumber as varchar(max)) hid
  from dbo.Correspondents
  where ParentID is null
  
  union all
  select c.*, h.hid + '.' + cast(c.OrderNumber as varchar(10)) 
  from dbo.Correspondents c
  join h on h.id= c.ParentID
  )
select *
from h
order by hid;

It can be refactored as a TVF as well

create function gethid(@id int) returns table
as return
    with h as (
      select *, cast(OrderNumber as varchar(max)) hid
      from dbo.Correspondents
      where ParentID is null
      
      union all
      select c.*, h.hid + '.' + cast(c.OrderNumber as varchar(10)) 
      from dbo.Correspondents c
      join h on h.id= c.ParentID
           -- stop when @id is found
           and h.id != @id
      )
    select *
    from h
    where id = @id;

db<>fiddle

MS SQL Server - How to create a view using hierarchical query

There's no hierarchical engine. There's the hierarchical type hierarchyid that can represent hierarchies and accelerate performance a lot, but from the comments it looks like you won't want to use it.

To query self-referencing hierarchies like this you can either join the table to itself up to a specific level, like you did, or use a Recursive Common Table Expression. A CTE is somewhat like defining a "view" in the query itself. An important difference is that the CTE can refer to itself, thus creating recursive CTEs.

This article explains how to use CTEs, using a hierarchical query as an example.

One part of the CTE selects the "root" rows. This is called the anchor,as this is where we start from.
The second, the recursive query, selects those related to the "previous" (the anchor's) results

In your case, the query would look something like :

With MyCTE
AS (
    --Anchor. Get the roots
    SELECT
        t.ID,
        NULL as ParentID
    FROM tablex 
    WHERE ParentID is null
    UNION ALL
    --Recursive. Get the direct descendants of the "previous" case
    SELECT 
        t.ID,
        t.ParentID
    FROM tablex t 
        INNER JOIN MyCTE m on m.ID=t.ParentID
    WHERE t.ParentID is NOT NULL
)
SELECT t.ID as CarID, t.ParentID
FROM MyCTE

To to get the level, we can add another column that starts with either 0 or 1, and increment it in the recursive query:

With MyCTE
AS (
    -- Anchor
    SELECT
        ID,
        ParentID,
        1 as Level.           -- Start with Level = 1
    FROM tablex 
    WHERE ParentID is null
    UNION ALL
    -- Recursive
    SELECT 
        t.ID,
        t.ParentID,
        m.Level+1 as Level.   -- Increment the level
    FROM tablex t 
        INNER JOIN MyCTE m on m.ID=t.ParentID
    WHERE t.ParentID is NOT NULL
)
SELECT 
    ID as CarID, 
    Level as CarLevel
FROM MyCTE

---

Related Topics