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
. 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