sqlserver Como Unir Duas Tabelas com Distribuição Exata de Dados no SQL Server?

[TavinhoBRMG]

Olá! Estou trabalhando com SQL Server.

Preciso unir duas tabelas com base na coluna “Ctrl”. A primeira tabela, “Cad_Clients”, contém informações de clientes, enquanto a segunda, “Cad_Colors”, possui a descrição das informações. A condição para uni-las é que o valor de “Ctrl” seja o mesmo em ambas as tabelas. No entanto, a distribuição precisa ser exata, ou seja, não podem ocorrer duplicatas.

Imagine que eu crie um laço ‘for’ na tabela ´Cad_Colors´. Em cada iteração, adiciono um registro da tabela à ‘Cad_Clients’, desde que o valor ‘Ctrl’ corresponda. Após, excluo esse registro de ´Cad_Colors´. Se a tabela em loop ficar vazia ou o laço chegar ao fim, significa que distribuí o máximo possível dos registros. É apenas isso, mas não quero usar um laço no banco; estou confiante de que, discutindo aqui, podemos obter um resultado muito mais conciso.

Representação visual do problema

Cad_Clients

    | idPerson | Person   | Ctrl  |
    |----------|----------|-------|
    | 1        | John     | 100   |
    | 2        | Sarah    | 101   |
    | 3        | Michael  | 102   |
    | 4        | Emily    | 103   |
    | 5        | William  | 104   |
    | 6        | Olivia   | 105   |
    | 7        | James    | 100   |
    | 8        | Emma     | 101   |
    | 9        | Benjamin | 102   |
    | 10       | Sophia   | 103   |
    | 11       | Samuel   | 104   |
    | 12       | Ava      | 105   |
    | 13       | Joseph   | 100   |

Cad_Colors

    | Ctrl | Color    | idColor |
    |------|----------|---------|
    | 100  | Red      | 1       |
    | 104  | Blue     | 2       |
    | 105  | Green    | 3       |
    | 103  | Yellow   | 4       |
    | 104  | Purple   | 5       |
    | 105  | Red      | 6       |

União das duas tabelas:

    | idPerson | Person  | Ctrl | Color  |
    |----------|---------|------|--------|
    | 1        | John    | 100  | Red    |
    | 4        | Emily   | 103  | Yellow |
    | 5        | William | 104  | Blue   |
    | 6        | Olivia  | 105  | Green  |
    | 11       | Samuel  | 104  | Purple |
    | 12       | Ava     | 105  | Red    |

Algumas tentativas frustradas:

INSERT INTO @Tmp
SELECT ccl.idPerson, ccl.Person, ccl.Ctrl, 
(SELECT TOP 1 cco.idColor FROM Cad_Colors cco WHERE cco.Ctrl = ccl.Ctrl AND cco.idColor NOT IN(SELECT idColor FROM @Tmp))
FROM Cad_Clients ccl

 

INSERT INTO @Tmp
SELECT ccl.idPerson, ccl.Person, ccl.Ctrl, cco.idColor, cco.Color
FROM Cad_Clients ccl
JOIN (SELECT TOP 1 cco.* FROM Cad_Colors cco WHERE cco.idColor NOT IN(SELECT idColor FROM @Tmp)) cco ON cco.Ctrl = ccl.Ctrl

 

INSERT INTO @Tmp
SELECT ccl.idPerson, ccl.Person, ccl.Ctrl, cco.idColor, cco.Color
FROM Cad_Clients ccl
JOIN Cad_Colors cco ON cco.Ctrl = ccl.Ctrl
WHERE NOT EXISTS (SELECT 1 FROM @Tmp WHERE Ctrl = cco.Ctrl)

 

MERGE INTO Cad_Clients AS target
USING Cad_Colors AS source
ON target.Ctrl = source.Ctrl
WHEN MATCHED THEN
UPDATE SET target.idPerson = target.idPerson
OUTPUT inserted.idPerson, inserted.Person, inserted.Ctrl, inserted.idColor, inserted.Color INTO @tmp;

Ajuda para criar os cenários:
 

CREATE TABLE Cad_Clients( [idPerson] [int] IDENTITY(1,1) NOT NULL, [Person] [varchar](60) NULL, [Ctrl] [int] NULL );
CREATE TABLE Cad_Colors( [Ctrl] [int] NULL, [Color] [varchar](60) NULL, [idColor] [int] IDENTITY(1,1) NOT NULL );
INSERT INTO Cad_Clients (Person, Ctrl) VALUES ('John',100), ('Sarah',101), ('Michael',102), ('Emily',103), ('William',104), ('Olivia',105), ('James',100), ('Emma',101), ('Benjamin',102), ('Sophia',103), ('Samuel',104), ('Ava',105), ('Joseph',100);
INSERT INTO Cad_Colors (Ctrl, Color) VALUES (100, 'Red'), (104, 'Blue'), (105, 'Green'), (103, 'Yellow'), (104, 'Purple'), (105, 'Red');
DECLARE @Tmp TABLE( idPerson INT, Person VARCHAR(60), Ctrl INT, idColor INT /*Color VARCHAR(60)*/ );

 

Editado Novembro 2, 2023 por TavinhoBRMG
Não estava na formatação do fórum

[Ronivaldo Lopes]

Bom dia, na tabela de Cad_Clients, cada cliente possui 1 Ctrl, na tabela de Cad_Colors, o Ctrl pode ter mais uma cor, resumindo a cliente pode ter mais de uma cor.

select a.*, b.Color
  from Cad_Clients a
  left join Cad_Colors b on a.Ctrl = b.Ctrl
  where b.Ctrl is not null
  order by a.idPerson
 
100    -> Red
103    -> Yellow
104    -> Blue e Purple
105    -> Green e Red

resultado da query

idperson	person	ctrl	color
1	John	100	Red
4	Emily	103	Yellow
5	William	104	Blue
5	William	104	Purple
6	Olivia	105	Red
6	Olivia	105	Green
7	James	100	Red
10	Sophia	103	Yellow
11	Samuel	104	Purple
11	Samuel	104	Blue
12	Ava	105	Red
12	Ava	105	Green
13	Joseph	100	Red