How to select records in an Auto-referenced table using Recursiveness?

Asked

Viewed 402 times

5

In a scenario of areas where one area can be supervised by another is representing in a tree structure as follows:

inserir a descrição da imagem aqui

Problem: The need to select the Área (CBT - Cubatão Industrial Complex) plus all the Áreas which follow the Hierarchy. (In this situation all the Areas listed in the image)

There will be situations where the Área(CBT- Infrastructure Management) could be requested, so that would be the result:

inserir a descrição da imagem aqui

  • How should the select to select this data?

Table Areas:

CREATE TABLE [dbo].[Areas](
 [IdArea] [int] IDENTITY(1,1) NOT NULL,
 [IdAreaPai] [int] NULL,
 [Nome] [varchar](50) NOT NULL)

Inserts:

insert into Areas(IdAreaPai, Nome)
values(null,'CBT - Complexo Industrial de Cubatão')


insert into Areas(IdAreaPai, Nome)
values(1,'CBT - Áreas Corporativas')

insert into Areas(IdAreaPai, Nome)
values(2,'CBT - Faturamento')

insert into Areas(IdAreaPai, Nome)
values(2,'CBT - Gerência de Tecnologia da Informação')

insert into Areas(IdAreaPai, Nome)
values(2,'CBT - Gerência de Controladoria')

insert into Areas(IdAreaPai, Nome)
values(2,'CBT - Gerência de Infraestrutura')

insert into Areas(IdAreaPai, Nome)
values(6,'CBT - Restaurante')

insert into Areas(IdAreaPai, Nome)
values(6,'CBT - Serviço de infraestrutura')

insert into Areas(IdAreaPai, Nome)
values(6,'CBT - Transporte')

insert into Areas(IdAreaPai, Nome)
values(2,'CBT - Gerência de Proj. Correntes Nitrogenados')

insert into Areas(IdAreaPai, Nome)
values(10,'CBT - Arquivo Técnico')

insert into Areas(IdAreaPai, Nome)
values(10,'CBT - Proj. Correntes')

insert into Areas(IdAreaPai, Nome)
values(2,'CBT - Gerência de Recursos Humanos')

 insert into Areas(IdAreaPai, Nome)
 values(null,'CBT - Complexo')
  • I leave you the idea of how to create select, at the moment I am not with pc to solve.: select all 2, for each result, select Where id-parent=id-area and for each of the selected, join select id-paiSelect=id-areaSelect.

  • @Pedroferreira any help is welcome :)

  • @Marconi with PC is simple and the guiding line too, beyond the link referenced by Henrique, looks for methodology to iterate a select

  • @Pedroferreira thanks for the tips, I will try to do this query by the end of the day, feel free to reply too :)

  • Similar: https://answall.com/q/214625/64969

  • @Jeffersonquesado I managed to understand the logic, but I did not understand why the select I added in the question did not work if I look for example by Idarea = 2

  • @Marconi like this, I would make the base case of the recursion different, then put a WHERE idAncestral = 2 external part of the consultation

  • In SQL Fiddle I could not run

Show 3 more comments

2 answers

3


  • If there’s anyone under "CBT - Restaurant" they won’t come.

  • @Juliosoares added a level below: insert into Areas(IdAreaPai, Nome)
values(7,'CBT - Restaurante 2')and the consultation listed this data

1

See if that’s what you need...

WITH ArvoreAreas AS 
( 
    SELECT 
        IdArea
        ,IdAreaPai
        ,1 as Level
        ,CAST(Nome as varchar(max)) as Nodes 
        ,IdArea as IdentificadorUnico
    FROM 
        Areas 
    WHERE 
        IdAreaPai is null

    UNION ALL

    SELECT 
        c.IdArea
        ,c.IdAreaPai
        ,Level + 1
        ,Cast(ac.Nodes + '->' + c.Nome as varchar(max))
        ,c.IdAreaPai as IdentificadorUnico
    FROM 
        Areas c 
        INNER JOIN ArvoreAreas ac ON c.IdAreaPai = ac.IdArea
)
SELECT * FROM ArvoreAreas 
WHERE Nodes like 'CBT - Complexo Industrial de Cubatão%'
ORDER BY Nodes
  • Your Sql Select the second line of the Inserts I added @Julio.

  • I edited... You can check the beginning of the Nodes field.. There will be the whole tree of your hierarchy

  • Julio, it would not be better to use the id of the higher hierarchy area as a condition of the recursive CTE root query?

  • It depends on the hierarchy... If there are other levels below, they will not come...

Browser other questions tagged

You are not signed in. Login or sign up in order to post.