SELECT in two tables, with ID separated by comma

Question

Hello! How to make a SELECT that returns data from the first table, whose "id" is separated by commas in the second table? Example:

tbl_cores
    id | cor
    1  | azul
    2  | verde
    3  | amarelo
    4  | vermelho

tbl_mesa
    id | cores
    1  | 2,4
    2  | 1,3
    3  | 2,3,4,1

=========== I need you to come back:

1 = verde vermelho
2 = azul amarelo
3 = verde amarelo vermelho azul

Thanks for any help!

Answer 1

The ideal, if you have access and can change the structure of this bank, would be to use a many-to-many ratio. This way you could map correctly, according to normal forms, and would not have problems when doing select in the tables. For example:

tbl_cores
    id | cor
    1  | azul
    2  | verde
    3  | amarelo
    4  | vermelho

tbl_mesa_cores
    id | id_mesa | id_cores
    1  | 1       | 2
    2  | 1       | 4
    3  | 2       | 1
    4  | 2       | 3
    5  | 3       | 2
    6  | 3       | 3
    7  | 3       | 4
    8  | 3       | 1

tbl_mesa
    id | desc
    1  | -
    2  | -
    3  | -

Answer 2

In that case, you can create a FUNCTION that returns a string with the name of the colors passing the IDS that you want, as follows.

create FUNCTION StringListToTexto
(
    @List varchar(MAX)
)
RETURNS varchar(max)
AS
BEGIN
    DECLARE @item varchar(800), @Pos int

    SET @List = LTRIM(RTRIM(@List))+ ','
    SET @Pos = CHARINDEX(',', @List, 1)

    declare @texto varchar(max) = '';

     ---- sua tabela substituar por sua tabela
    declare @tbl_coresAux TABLE (id INT, cor VARCHAR(50))
    INSERT INTO @tbl_coresAux VALUES 
    (1  ,' azul'),
    (2  ,' verde'),
    (3  ,' amarelo'),
    (4  ,' vermelho')
    -----

    WHILE @Pos > 0
    BEGIN
        SET @item = LTRIM(RTRIM(LEFT(@List, @Pos - 1)))
        IF @item <> ''
        BEGIN
            declare @IdAux int = (CAST(@item AS int));

            set @texto = @texto +  (Select cor from @tbl_coresAux where id = @IdAux);
        END
        SET @List = RIGHT(@List, LEN(@List) - @Pos)
        SET @Pos = CHARINDEX(',', @List, 1)
    END

    RETURN @texto
END

And you’d call it like this.

declare @tbl_cores TABLE (id INT, cor VARCHAR(50))
INSERT INTO @tbl_cores VALUES 
(1  ,' azul'),
(2  ,' verde'),
(3  ,' amarelo'),
(4  ,' vermelho')

declare @tbl_mesa TABLE (id INT, cores VARCHAR(50))
INSERT INTO @tbl_mesa VALUES 
(1  ,'2,4'),
(2  ,'1,3'),
(3  ,'2,3,4,1')



select id, ([dbo].[StringListToTexto] (cores)) from @tbl_mesa
where id = 2

Answer 3

Try it this way:

select tbl_mesa.id, tbl_cores.cor 
from tbl_mesa inner join tbl_cores
       on ',' + tbl_mesa.cores + ',' like '%,' + cast(tbl_cores.id as nvarchar(20)) + ',%'