3

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,

  • Can you include sample data and expected output? Use db<>fiddle to make this easier. – HandyD Apr 30 at 0:09
  • CromixPT, if you want @HandyD to know that you updated the question as he asked, you should also answer his comment reffering to him. – Ronaldo Apr 30 at 12:39
  • @Ronaldo thank you :) Still new arround here but always learning. – CromixPT Apr 30 at 16:37
  • @HandyD i think i updated the question with all info you asked. Is that enough? – CromixPT Apr 30 at 16:37
  • I can't seem to understand the relation between the expected result and the data in the dbfiddle. For example, why is 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)? – Ronaldo Apr 30 at 21:05
2

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;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.