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

There's no hierarchical engine. A recursive query won't run any faster than a JOIN. You'd get better performance if you used `hierarchyid` instead of self-referencing ID/Parent_ID. *That* would eliminate all joins and convert a child search to a simple range search — Panagiotis Kanavos, May 18 '21 at 12:34
A `hierarchyid` value is equivalent to a binary path of the node IDs, eg `1/3/8/678`. When you search for children of `1/3` the server will search for all`hierarchyid` values starting with `1/3`, in a single pass. — Panagiotis Kanavos, May 18 '21 at 12:37
I think what you are looking for is called a recursive common table expression. A good example of that can be found here: https://www.sqlservertutorial.net/sql-server-basics/sql-server-recursive-cte/ — SchmitzIT, May 18 '21 at 12:39
Why are you nesting `COALESCE` functions? `COALESCE`, unlike `ISNULL` allows for 2+ parameters. — Thom A, May 18 '21 at 12:40
Also, why the nested `ON` clauses? When they are are `LEFT JOIN`s there is no need for it. — Thom A, May 18 '21 at 12:41
@PanagiotisKanavos thanks for informations but for this case i need to convert this view to hierarchical query for now. — satcha, May 18 '21 at 12:49
@satcha You need a recursive CTE for this. Again, you won't gain any performance from this. At best it would be as fast as the JOINs. You need a recursive CTE only if you want to handle more than 4 levels. `COALESCE` isn't that expensive. [This article shows how to use recursive CTEs](https://blog.sqlauthority.com/2012/04/24/sql-server-introduction-to-hierarchical-query-using-a-recursive-cte-a-primer/) for hierarchical queries — Panagiotis Kanavos, May 18 '21 at 13:28
@PanagiotisKanavos great i ll check this article hope he ll help me to solve this conversion thank you — satcha, May 18 '21 at 14:44
@satcha why are you joining on `Title` and `C_Id` though, but only returning `C_ID` ? — Panagiotis Kanavos, May 18 '21 at 15:22
@PanagiotisKanavos i am confusedwith this but i am trying to use only C_ID — satcha, May 19 '21 at 13:32
The general advantage of a recursive CTE/View is that it will work for any number of hierarchy levels. If you have a small fixed number of hierarchy levels (like say four or less) then you are generally better off using fixed JOINs. If you have a (potentially) unlimited number of hierarchy levels then you'd generally be better of with a recursive query with this table design. In some cases, depending on what you are trying to do, you can change the table design to use path-keys (`hierarchyid`) instead which can also handle a variable number of levels (if the max levels is not too high). — RBarryYoung, May 19 '21 at 13:33
Is this a class assignment of a work problem? If the latter, then could you tell us more about what/how this view will be used? Because currently it has no useful columns (other than the root-id) and no WHERE clause, so it's hard to tell what the application of this query is going to be (which would affect any good answer). — RBarryYoung, May 19 '21 at 13:45

score 3 · Accepted Answer · answered May 19 '21 at 14:14

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

thanks a lot, i used this method i mean CTE but i didn't get the same result as 4JOINS ( missing some records). can i use coalesce() this case ? — satcha, May 20 '21 at 12:04
@satcha `COALESCE` returns the first non-null value in a list of values, it doesn't affect what records are returned. If you wanted the first non-null ID you could use `COALESCE(d.C_ID,c.C_ID,b.C_ID,a.CID,t.C_ID)`. You didn't post the actual table schema, data or what you expect so it's not possible to create a query that returns what you want. — Panagiotis Kanavos, May 20 '21 at 13:10
`With MyCTE AS ( --Anchor. Get the roots SELECT C_ID, C_ID as ParentID FROM tablex WHERE title is null UNION ALL --Recursive. Get the direct descendants of the "previous" case SELECT t.C_ID, m.ParentID FROM tablex t INNER JOIN MyCTE m on t.ID=v.title ) SELECT ID .ParentID FROM MyCTE ` the correct answer. — satcha, May 26 '21 at 13:37

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

1 Answers1