Bases de datos

SQL Server

Consulta a tabla con llave foránea recursiva

Última actualización: 14-11-2017 17:51

Notas

Durante el desarrollo de aplicaciones con bases de datos muchas veces se recurre al concepto de una tabla que se hace referencia a sí misma.

Por ejemplo para crear una clasificación tipo "árbol" de productos, cuentas bancarias e innumerables casos más.

En este ejemplo, se asume una tabla llamada "EB_CTA" en donde el campo "Id_Ref" hace referencia a la llave primaria de la misma tabla.

El asunto a tratar con el código aquí expuesto es:
"¿Cómo obtener con un consulta, a partir de la llave primaria de uno de los registros, la lista de los antecesores en el árbol de dicho registro?".

En otras palabras, si se tiene la llave de un registro, sacar una lista que contenga a ese registro, a su padre,a su abuelo, a su bisabuelo.. etc., hasta llegar al ancestro que no apunte a nadie.

El buen funcionamiento de este código ha sido probado en SQL Server 2000 y SQL Server Express 2005, 2008, 2008R2, 2012, 2014 y 2016.

Para resolver el problema se propone la creación de una función que devuelva una tabla conteniendo el listado de las llaves primarias de todos los registros antecesores de un registro específico incluyendo a este último.

En el ejemplo se asume que cuando "Id_Ref" sea nulo se tratará de un registro sin padre. Para hacer más obvio al ojo el buen funcionamiento de la consulta se ha dispuesto la descripción de los registros de forma que el prefijo de cada descripción defina en que nivel está de esta forma:

• La descripción no afectará en modo alguna a la consulta porque esta se basa en las llaves de los registros.
• Cuando la "clasificación tipo árbol" sea de muchos niveles, existen mejores métodos que el aquí expuesto para conseguir una consulta como esta.

Comandos SQL

/* Poner el nombre de la base de datos a utilizar*/
use northwind
go
 
-- Eliminación de tabla de pruebas 
if exists (select [id] from sysobjects where xtype = 'u' and [name] = 'EB_CTA') drop table EB_CTA
go
 
-- Eliminación de función auxiliar 
if exists (select [id] from sysobjects where xtype = 'TF' and [name] = 'EB_Get_CTA_Tree') drop function dbo.EB_Get_CTA_Tree
go
 
-- Creación de tabla de pruebas 
create table EB_CTA
(
Id_Cta int identity (1, 1) not null,
Id_Ref int null,
Descripcion varchar(100) not null,
constraint PK_EB_CTA primary key (Id_Cta)
)
go
 
alter table EB_CTA add constraint FK_EB_CTA_EB_CTA foreign key (Id_Ref) references EB_CTA (Id_Cta)
go
 
-- Creación de función auxiliar 
create function dbo.EB_Get_CTA_Tree (@Id_Cta int) returns @t table (Id_Row int identity(1, 1), Id_Cta int, Id_Ref int) as
begin
declare @N tinyint
declare @Id_Ref int
 
	set @Id_Ref = (select Id_Ref from EB_CTA where Id_Cta = @Id_Cta)
	insert into @t (Id_Cta, Id_Ref) values (@Id_Cta, @Id_Ref)
	while (not isnull(@Id_Ref, -1) = -1)
	begin
		set @Id_Cta = @Id_Ref
		set @Id_Ref = (select Id_Ref from EB_CTA where Id_Cta = @Id_Cta)
		insert into @t (Id_Cta, Id_Ref) values (@Id_Cta, @Id_Ref)
	end
 
return
 
end
go
 
-- Inserción de registros de prueba 
set nocount on
insert into EB_CTA (Descripcion) values ('BAS1')
insert into EB_CTA (Descripcion) values ('BAS2')
insert into EB_CTA (Descripcion) values ('BAS3')
insert into EB_CTA (Descripcion) values ('BAS4')
insert into EB_CTA (Descripcion) values ('BAS5')
 
insert into EB_CTA (Descripcion, Id_Ref) values ('BAS1_GGF1', 1)
insert into EB_CTA (Descripcion, Id_Ref) values ('BAS1_GGF2', 1)
insert into EB_CTA (Descripcion, Id_Ref) values ('BAS1_GGF3', 1)
insert into EB_CTA (Descripcion, Id_Ref) values ('BAS1_GGF4', 1)
insert into EB_CTA (Descripcion, Id_Ref) values ('BAS1_GGF5', 1)
 
insert into EB_CTA (Descripcion, Id_Ref) values ('BAS1_GGF1_GF1', 6)
insert into EB_CTA (Descripcion, Id_Ref) values ('BAS1_GGF1_GF2', 6)
insert into EB_CTA (Descripcion, Id_Ref) values ('BAS1_GGF1_GF3', 6)
insert into EB_CTA (Descripcion, Id_Ref) values ('BAS1_GGF1_GF4', 6)
insert into EB_CTA (Descripcion, Id_Ref) values ('BAS1_GGF1_GF5', 6)
 
insert into EB_CTA (Descripcion, Id_Ref) values ('BAS1_GGF2_GF1', 7)
insert into EB_CTA (Descripcion, Id_Ref) values ('BAS1_GGF2_GF2', 7)
insert into EB_CTA (Descripcion, Id_Ref) values ('BAS1_GGF2_GF3', 7)
insert into EB_CTA (Descripcion, Id_Ref) values ('BAS1_GGF2_GF4', 7)
insert into EB_CTA (Descripcion, Id_Ref) values ('BAS1_GGF2_GF5', 7)
 
insert into EB_CTA (Descripcion, Id_Ref) values ('BAS1_GGF3_GF1', 8)
insert into EB_CTA (Descripcion, Id_Ref) values ('BAS1_GGF3_GF2', 8)
insert into EB_CTA (Descripcion, Id_Ref) values ('BAS1_GGF3_GF3', 8)
insert into EB_CTA (Descripcion, Id_Ref) values ('BAS1_GGF3_GF4', 8)
insert into EB_CTA (Descripcion, Id_Ref) values ('BAS1_GGF3_GF5', 8)
 
insert into EB_CTA (Descripcion, Id_Ref) values ('BAS1_GGF4_GF1', 9)
insert into EB_CTA (Descripcion, Id_Ref) values ('BAS1_GGF4_GF2', 9)
insert into EB_CTA (Descripcion, Id_Ref) values ('BAS1_GGF4_GF3', 9)
insert into EB_CTA (Descripcion, Id_Ref) values ('BAS1_GGF4_GF4', 9)
insert into EB_CTA (Descripcion, Id_Ref) values ('BAS1_GGF4_GF5', 9)
 
insert into EB_CTA (Descripcion, Id_Ref) values ('BAS1_GGF4_GF5_F1', 30)
insert into EB_CTA (Descripcion, Id_Ref) values ('BAS1_GGF4_GF5_F2', 30)
insert into EB_CTA (Descripcion, Id_Ref) values ('BAS1_GGF4_GF5_F3', 30)
 
insert into EB_CTA (Descripcion, Id_Ref) values ('BAS1_GGF4_GF5_F3_S1', 33)
insert into EB_CTA (Descripcion, Id_Ref) values ('BAS1_GGF4_GF5_F3_S2', 33)
insert into EB_CTA (Descripcion, Id_Ref) values ('BAS1_GGF4_GF5_F3_S3', 33)
 
insert into EB_CTA (Descripcion, Id_Ref) values ('BAS1_GGF1_GF5_F1', 15)
insert into EB_CTA (Descripcion, Id_Ref) values ('BAS1_GGF1_GF5_F1_S1', 37)
set nocount off
go
 
-- Pruebas de consulta 
select * from EB_CTA where Id_Cta = 38
select b.Id_Cta, b.Id_Ref, b.Descripcion from dbo.EB_Get_CTA_Tree(38) a inner join EB_CTA b on a.Id_CTA = b.Id_CTA order by a.Id_Row desc
select b.Id_Cta, b.Id_Ref, b.Descripcion from dbo.EB_Get_CTA_Tree(31) a inner join EB_CTA b on a.Id_CTA = b.Id_CTA order by a.Id_Row desc
 

Resultado

Id_Cta      Id_Ref      Descripcion            
----------- ----------- -----------------------
38          37          BAS1_GGF1_GF5_F1_S1    

(1 row(s) affected)

Id_Cta      Id_Ref      Descripcion             
----------- ----------- ------------------------
1           NULL        BAS1
6           1           BAS1_GGF1
15          6           BAS1_GGF1_GF5
37          15          BAS1_GGF1_GF5_F1
38          37          BAS1_GGF1_GF5_F1_S1

(5 row(s) affected)

Id_Cta      Id_Ref      Descripcion             
----------- ----------- ------------------------
1           NULL        BAS1
9           1           BAS1_GGF4
30          9           BAS1_GGF4_GF5
31          30          BAS1_GGF4_GF5_F1

(4 row(s) affected)