How to recur SQL server using WITH to return all children

Question

I am trying to make a recursive in a single table called local where there is a localSuperior ID, referencing the same table.

If I have my biggest place with ID 1664, when making the recursive, it returns me the places below this place. However, I needed to return to the low places of the children too, how to do?

Follows my code:

WITH lugar AS 
(
    SELECT l.[Id], [l].[LocalSuperiorId], l.[Nome]
    FROM [RM].[Local] AS [l]
    WHERE [l].[LocalSuperiorId] IN (1664
    )

    UNION ALL

    SELECT l2.[Id], l2.[LocalSuperiorId], l2.[Nome] 
    FROM [RM].[Local] AS [l2]
    INNER JOIN lugar p ON p.[LocalSuperiorId]=[l2].[Id]
) 
SELECT DISTINCT *
FROM lugar
WHERE lugar.[LocalSuperiorId] IS NOT NULL

Answer 1

Hello, expressions using WITH are called CTE’s (Common Table Expressions). You need a recursive CTE. You have done well the union of two queries with UNION.

1) What you need is for the first UNION query to represent the initial case, as you already did, and the second query to represent the recursive case, with a small modification.

WITH lugar AS 
(
    SELECT 1 [nivel], l.[Id], [l].[LocalSuperiorId], l.[Nome]
    FROM [RM].[Local] AS [l]
    WHERE [l].[LocalSuperiorId] IN (1664)

    UNION ALL

    SELECT lugar.nivel+1 [nivel], l2.[Id], l2.[LocalSuperiorId], l2.[Nome] 
    FROM [RM].[Local] AS [l2]
    INNER JOIN lugar p ON p.[Id] = [l2].[LocalSuperiorId]
) 
SELECT DISTINCT *
FROM lugar
WHERE lugar.[LocalSuperiorId] IS NOT NULL

I made a change in your condition

INNER JOIN lugar p ON p.[LocalSuperiorId]=[l2].[Id]

for

INNER JOIN lugar p ON p.[Id] = [l2].[LocalSuperiorId]

Because the initial case of CTE starts at the root (first level), and the recursive case takes what is below, then it is the low level that points to the top location, not the other way around.

I also put a "level" column, which returns the level of depth in which we find ourselves in the hierarchy, can be interesting.

2) Your query, the way it is, does not return the root department, with its name, data, etc. The 1664 department. Wouldn’t it be interesting to display it? If yes, just a small change.

WITH lugar AS 
(
    SELECT 0 [nivel], l.[Id], [l].[LocalSuperiorId], l.[Nome]
    FROM [RM].[Local] AS [l]
    WHERE [l].[Id] IN (1664)

    UNION ALL

    SELECT lugar.nivel+1 [nivel], l2.[Id], l2.[LocalSuperiorId], l2.[Nome] 
    FROM [RM].[Local] AS [l2]
    INNER JOIN lugar p ON p.[Id] = [l2].[LocalSuperiorId]
) 
SELECT DISTINCT *
FROM lugar
WHERE lugar.[LocalSuperiorId] IS NOT NULL

3) Finally, you should not need to give a SELECT DISTINCT at the end, because supposedly each department cannot appear more than once in the hierarchy. A common SELECT would suffice. If a department appeared more than once in the final result, there could be only two possibilities. The first is that a department belonged to more than one higher department. This case does not occur due to its modeling of tables only allow the identifier of a single higher department. The other possibility is that one department contains another, which in turn contains another, and so on, until it arrives in a department that contains the department of origin. This would indicate a circularity in the definition of references, which is an inconsistency and your query would be in loop.

In addition, it is not necessary to test "WHERE PLACE. [Localsuperiorid] IS NOT NULL", as in the initial case it satisfies the condition "WHERE [l]. [Localsuperiorid] IN (1664)", and can never be null; and in the second case, it satisfies Join with some other record, so what has been returned can never be null, either.

Then the final query would look like this:

WITH lugar AS 
(
    SELECT 0 [nivel], l.[Id], [l].[LocalSuperiorId], l.[Nome]
    FROM [RM].[Local] AS [l]
    WHERE [l].[Id] IN (1664)

    UNION ALL

    SELECT lugar.nivel+1 [nivel], l2.[Id], l2.[LocalSuperiorId], l2.[Nome] 
    FROM [RM].[Local] AS [l2]
    INNER JOIN lugar p ON p.[Id] = [l2].[LocalSuperiorId]
) 
SELECT *
FROM lugar

An interesting problem would be to sort the result so as to show the hierarchical structure, that is, for each returned record, the following records would be its sub-portions alphabetically, and so on.