Get the total count of every child foreign keys

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

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;

StackExchange Database Administrators Q#266128, answer score: 2