Get the total count of every child foreign keys

Question

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:

1. The total count of connections of each child (say its lvl 3 for example)
2. For each lvl2 equipement the total sum of the of its lvl3' child conections
3. 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,

Answer 1

Yes, it's possible to do it using either a recursive CTE or a recursive function (at first I couldn't figure out how to solve it using CTE, so I coded the solution using the function and, since it might be useful to someone else, I'm keeping it on the answer as well). Here's how you can do it:

Recursive CTE

;WITH QUERY AS
(
    SELECT IDEquip, 1 AS Quantity
    FROM Conns

    UNION ALL

    SELECT E.IDParent AS IDEquip, 1 AS Quantity
    FROM Equipament E
        INNER JOIN QUERY Q ON E.ID = Q.IDEquip
)
SELECT E.ID, ISNULL(SUM(Q.Quantity), 0) AS Total 
FROM Equipament E
    LEFT JOIN QUERY Q ON E.ID = Q.IDEquip
GROUP BY E.ID;

The trick to solve it using the recursive CTE was to query the values from back to front.

Recursive Function

CREATE FUNCTION dbo.GetAllDescendants(@id nvarchar(100))
RETURNS INT
AS 
BEGIN
    RETURN(
        SELECT SUM(Total + ISNULL(dbo.GetAllDescendants(ID), 0)) 
        FROM (
            SELECT ID, IDParent, COUNT(IDEquip) AS Total
            FROM Equipament 
                LEFT JOIN Conns ON ID = IDEquip
            GROUP BY ID, IDParent
        ) AS TotalConns
        WHERE IDParent = @id
    )
END;

With this query you can get the total of descendants, but not the connections for the ID itself:

SELECT ID, ISNULL(dbo.GetAllDescendants(ID), 0) AS [Total Conections Dependancy]
FROM Equipament
ORDER BY ID;

To get the exact result you described you should run it like this:

SELECT ID, IIF(COUNT(IDEquip) > 0, COUNT(IDEquip), ISNULL(dbo.GetAllDescendants(ID), 0)) AS [Total Conections Dependancy]
FROM Equipament 
    LEFT JOIN Conns ON ID = IDEquip
GROUP BY ID, IDParent
ORDER BY ID;