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

Question

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

Asked 7 years, 4 months ago

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:

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:

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.

– Pedro Ferreira

2017/06/27 at 14:28
@Pedroferreira any help is welcome :)

– Marconi

2017/06/27 at 14:29
@Marconi with PC is simple and the guiding line too, beyond the link referenced by Henrique, looks for methodology to iterate a select

– Pedro Ferreira

2017/06/27 at 14:35
@Pedroferreira thanks for the tips, I will try to do this query by the end of the day, feel free to reply too :)

– Marconi

2017/06/27 at 14:41
Similar: https://answall.com/q/214625/64969

– Jefferson Quesado

2017/06/27 at 15:01
@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

2017/06/27 at 15:04
@Marconi like this, I would make the base case of the recursion different, then put a WHERE idAncestral = 2 external part of the consultation

– Jefferson Quesado

2017/06/27 at 15:20
In SQL Fiddle I could not run

– LCarvalho

2019/09/10 at 16:04

Show 3 more comments

2 answers

3

Through the questions:

I managed to reach the select below. In my case the stopping point will be the own Área.

  WITH hierarquia AS
      ( SELECT *
       FROM Areas
       WHERE IdArea = 6
         UNION ALL
         SELECT t.*
         FROM Areas t
         INNER JOIN hierarquia s ON t.IdAreaPai = s.IdArea )
    SELECT *
    FROM hierarquia

Upshot:

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

– Julio Soares

2017/06/27 at 16:58
@Juliosoares added a level below: insert into Areas(IdAreaPai, Nome)
values(7,'CBT - Restaurante 2')and the consultation listed this data

– Marconi

2017/06/27 at 17:00

Browser other questions tagged sql sql-server

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

by Julio Soares • **525** points · Answer 1 · 2017-06-27T14:55:24+00:00

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