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
@Pedroferreira any help is welcome :)
– Marconi
@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
@Pedroferreira thanks for the tips, I will try to do this query by the end of the day, feel free to reply too :)
– Marconi
Similar: https://answall.com/q/214625/64969
– Jefferson Quesado
@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
@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
In SQL Fiddle I could not run
– LCarvalho