select @@version;

CREATE TABLE GRUPO_CLIENTES ( CODGRUPO INT ,CPF_CNPJ VARCHAR(15) ) CREATE TABLE CLIENTES ( CODCLIENTE INT PRIMARY KEY IDENTITY ,NOME VARCHAR(50) ,CPF_CNPJ VARCHAR(15) ) INSERT INTO CLIENTES VALUES ('AURORA', '37306895000105') ,('SADIA','37306895000222') ,('PERDIGAO','37306895000333') ,('SEARA','37306895000444') ,('EMIRATES', '55172540000144') ,('AZUL','55172540000222') ,('TAM S/A','55172540000333') ,('GOL LINHAS AEREAS S/A','55172540000444') ,('FISHER PRICE','62085953000157') ,('RI HAPPY','62085953000222') ,('ESTRELA','62085953000333') ,('MATEL','62085953000444') ,('DAYCOVAL','06877315000106') ,('ITAU','06877315000222') ,('SANTANDER','06877315000333') ,('BRADESCO','06877315000444') ,('HABIBS','09429621000160') ,('MCDONALDS','09429621000111') ,('BOBS','09429621000222') ,('BURGER KING','09429621000333') ,('BMW','82779204000137') ,('FORD','82779204000222') ,('FIAT','82779204000333') ,('CHEVROLET','82779204000444') INSERT INTO GRUPO_CLIENTES VALUES (10, '37306895000105') ,(10,'37306895000222') ,(10,'37306895000333') ,(10,'37306895000444') ,(10,'37306895000555') ,(15, '55172540000144') ,(15,'55172540000222') ,(15,'55172540000333') ,(15,'55172540000444') ,(15,'30933404000555') ,(20,'62085953000157') ,(20,'62085953000222') ,(20,'62085953000333') ,(20,'62085953000444') ,(20,'62085953000555') ,(25,'06877315000106') ,(25,'06877315000222') ,(25,'06877315000333') ,(25,'06877315000444') ,(25,'06877315000555') ,(30,'09429621000160') ,(30,'09429621000111') ,(30,'09429621000222') ,(30,'09429621000333') ,(35,'82779204000137') ,(35,'82779204000222') ,(35,'82779204000333') ,(35,'82779204000444') ,(35,'82779204000555') CREATE TABLE MAPA_RISCO_VINCULOS ( ID_VINCULO INT PRIMARY KEY IDENTITY ,CNPJ_VINCULO VARCHAR(15) ,CNPJ_ASSOCIADO VARCHAR(15) ,ATIVO BIT ) INSERT INTO MAPA_RISCO_VINCULOS VALUES ( '37306895000105', '55172540000333', 1) -- AURORA+ TAM 10 + 15 ,( '62085953000444', '37306895000444', 1) -- MATEL + SEARA 20 + 10 ,( '82779204000333', '62085953000222', 1) -- FIAT + RI HAPPY 35 + 20 ,('06877315000444', '09429621000160' , 1) -- BRADESCO + HABIBS 25 + 30

DECLARE @vinculoCom VARCHAR(15) DECLARE @codgrupo INT SET @vinculoCom = '82779204000137' SET @codgrupo = (SELECT codgrupo FROM grupo_clientes WHERE cpf_cnpj = @vinculoCom) BEGIN WITH ctevinculos (cpf_cnpj) AS ( -- Initialization excerpt below. SELECT cpf_cnpj FROM grupo_clientes WHERE codgrupo = @codgrupo UNION ALL -- Recursive excerpt below. SELECT gc2.cpf_cnpj FROM mapa_risco_vinculos mrv INNER JOIN ctevinculos v ON mrv.cnpj_vinculo = v.cpf_cnpj INNER JOIN grupo_clientes gc1 ON mrv.cnpj_associado = gc1.cpf_cnpj INNER JOIN grupo_clientes gc2 ON gc1.codgrupo = gc2.codgrupo ) SELECT c.codcliente, c.nome, c.cpf_cnpj FROM clientes c JOIN ctevinculos v ON c.cpf_cnpj = v.cpf_cnpj END