How can i modify it so that it only gives colors to those that do not have colors.
DECLARE @Employees TABLE
(
Name VARCHAR(10)
)
INSERT INTO @Employees (Name)
VALUES ('Sam'), ('John'), ('Jack'), ('April'), ('Sonny'), ('Jill'), ('Jane');
DECLARE @Colors TABLE
(
Name VARCHAR(10)
)
INSERT INTO @Colors (Name)
VALUES ('Red'), ('Green'), ('Blue');
DECLARE @ColorCount INT
SELECT @ColorCount = COUNT(*) FROM @Colors
CREATE TABLE #Employees(SNumber INT IDENTITY(1,1), Name VARCHAR(10));
INSERT INTO #Employees(Name) SELECT Name FROM @Employees;
CREATE TABLE #Colors(CNumber INT IDENTITY(1,1), Name VARCHAR(10));
INSERT INTO #Colors(Name) SELECT Name FROM @Colors;
SELECT E.Name, C.Name FROM #Employees E
INNER JOIN #Colors C ON
CASE
WHEN (E.SNumber % @ColorCount) = 0
THEN @ColorCount
ELSE E.SNumber % @ColorCount
END = C.CNUmber
DROP TABLE #Employees;
DROP TABLE #Colors;