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

Question

I would like to create hierarchical query using with and removing 4 join, someone can help please i am beginner in SQL views.

CREATE VIEW CAR( ID, CARLEVEL) AS    
select
t.C_ID,
coalesce(d.C_ID, coalesce(c.C_ID, coalesce(b.C_ID, 
coalesce(a.C_ID, t.C_ID))))
from tablex t LEFT OUTER JOIN tablex a LEFT OUTER JOIN tablex b 
LEFT OUTER JOIN tablex c 
LEFT OUTER JOIN tablex d ON c.Title = d.C_ID ON b.Title = c.C_ID 
ON a.Title = b.C_ID ON t.Title = a.C_ID

content of Tablex is :

 C_ID    Title         
 67       Null
 68       Null
 69       68
 70       68
 71       68
 72       Null
 81       Null
 82       81
 83       81
 84       Null
 86       Null
 87       Null
104       81
105       81
106       81
107       Null
4707      81

what i expect with CTE is :

ID    CAR LEVEL
69     68
70     68
71     68
68     68
82     81
83     81
104    81
105    81
106    81
4707  81
81     81

Answer 1

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