SELECT Recursive (SQL)

Question

Good afternoon!

Guys I’m having the following difficulty:

Imagine that I have a list of several Customers, and each customer has a Rating Group ( the Rating Group can be repeated for some customers);
Suddenly some customers have a bond with other customers;
I need to create a Link Map where when searching for a client I can display:

• all customers belonging to the same Group as the searched customer;
• all clients of the group to which the clients of the Core Group may be linked Example :

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, '30933404000107')
        ,(15,'30933404000222')
        ,(15,'30933404000333')
        ,(15,'30933404000444')
        ,(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(14)
        ,CNPJ_ASSOCIADO VARCHAR(14)
        ,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 

1. When I search for the CNPJ 06877315000222 (ITAU) has display:
• all customers of the ITAU group (BRADESCO, ITAU, SANTANDER, CAIXA), because the Bradesco client was linked to the HABIBS client;
• all customers of the HABIBS group (HABIBS, MCDONALDS, BURGER KING, BOBS)
2. Already when I search the CNPJ 82779204000137 (BMW) have to display:
• all customers of BMW group (BMW, FIAT, CHEVROLET, FORD) because Fiat customer was linked with RI HAPPY customer;
• all customers of the RI HAPPY group ( RI HAPPY, MATEL, ESTRELA , FISHER PRICE);
• all customers of the SEARA group (AURORA, SEARA, PERDIGAO, SADIA) because it was linked to the client MATEL;
• all customers of the TAM group (EMIRATES, TAM, GOL, AZUL) because it was linked to the AURORA customer who is in the same group as the SEARA previously linked;

I was trying to do the following procedure:

1. I check the CODGRUPO in which the searched customer ('BMW' ,82779204000137) is part and store in a temporary table all Cnpjs of this group;
2. I store all the links these customers have;

And this is where I get lost. I’m not finding a way to check whether customers linked to the core group have ties to other customers from other groups and display them all in one place. I’m even trying to do it with CTE Recursivo, but I’m not fitting the necessary logic. Who can give ideas I thank.

Answer 1

Below is a proposal for consultation.

The strategy in writing a recursive CTE is to start from an initial set of elements and, in the recursive step, to merge that initial set with the set of elements you want to recursively obtain.

That is, in this specific case, the initial set is the set of companies that are in the same group as the initial link company (I chose the CNPJ 82779204000137, to be more didactic).

The junction set is the junction of the initial set with the link map rule (via field cnpj_vinculo), in conjunction with the group of companies of each association of the link map (via field cnpj_associado), in conjunction with the group of undertakings of the same group (via field codgrupo).

During the execution of this CTE, at each "round" of the recursive chunk, the initial set grows a little more, feedback the recursive chunk, until there is no more element of the new set that can map new elements for you. That is, until the number of TEC elements converges.

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

See the fiddle here.