I have been trying this in SQL EXPRESS 2016. Here is my problem:
I have a tree like structure DB(Parent > child > grandchild >...) and in the lower level there is a relation with another table.
Table 1
CREATE TABLE [dbo].[Equipament]
[ID] [nvarchar](50) NOT NULL,
[AL] [nvarchar](50) NULL,
[Lvl] [nvarchar](50) NULL,
[IDParent] [nvarchar](50) NULL,
Table 2
CREATE TABLE [dbo].[Conns]
[Conns] [nvarchar](50) NOT NULL,
[IDEquip] [nvarchar](50) NOT NULL,
FOREIGN KEY([IDEquip])
REFERENCES [dbo].[Equipament] ([ID])
What I'm trying to get is a CTE that in a recursive way gets:
- The total count of connections of each child (say its lvl 3 for example)
- For each lvl2 equipement the total sum of the of its lvl3' child conections
- For each lvl1 equipment the total sum of its childs
My attempts have failed mainly because there are no level 1 and 2 equipments in the Conns table, and cant have a LEFT JOIN
in the recursive CTE.
The closest I got was to this:
;WITH QUERY AS
(
SELECT E.ID,E.IDParent,L.conns
FROM Equipament E
LEFT JOIN conns L ON E.ID=L.IDEquip
WHERE E.IDParent IS NULL
UNION ALL
SELECT E.ID,E.IDParent,L.conns
FROM Equipament E
JOIN conns L ON E.ID = L.IDEquip
JOIN QUERY P on P.ID = E.IDParent
)
SELECT
E.ID,
SUM(S.Total) AS LTotal
FROM Equipament E
LEFT JOIN ( SELECT Q.ID, COUNT(Q.conns) AS Total
FROM QUERY Q
GROUP BY Q.ID
) as S
ON E.ID = S.ID
GROUP BY E.ID
ORDER BY E.ID
option (maxrecursion 0)
EDIT The fiddles requested: dbfiddle link
With the example data the expected output should be something like:
+---+-------------------------------------+
|ID | Total Conections Dependancy |
+---+-------------------------------------+
|AA | 1 (CE + BA) |
|AB | 4 ( BB + BC) |
|BA | 0 |
|BB | 0 |
|BC | 4 ( CD + CB) |
|CA | 0 |
|CB | 3 |
|CD | 1 |
|CE | 1 |
+---+-------------------------------------+
Only the C level equipemnts have connections, and there might be a direct jump from level A to level C.
Is this possible to do? Can someone point me in the right direction?
Thank you,
Total Conections Dependancy
1 for AA if it has 2 connections (CE + BA)? Or why is AB 4 if it has 5 children and grandchildren (BC + BB + CB + CD + CA)?