If I've got a table:

CREATE TABLE Kids (
    kid_id int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    kid_name varchar(45) NOT NULL,
    kid_favcolors (text) NULL,
    PRIMARY_KEY(kid_id)
    )

and I've got a table:

CREATE TABLE Colors (
    color_id int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    color_name varchar(45) NOT NULL,
    PRIMARY_KEY(color_id)
)

With rows that reference favorite colors via comma separated ids:

INSERT INTO Kids(kid_name, kid_favcolors) VALUES('Joe','1,2,3,4,5');
INSERT INTO Kids(kid_name, kid_favcolors) VALUES('Mary','1,2,3');

Wouldso would I produce a statement that will return each entry within the Kid database using the Kid_title and also the color_title of all the ids recommended within the kid_favcolors column.

For instance:

COLORS:

color_id    color_title

1           yellow

2           eco-friendly

3           blue

4           crimson

5           red-colored

6           brown

7           black

KIDS:

kid_id      kid_title    kid_favcolors

1           Joe         1,2,3,4,5

2           Mary        1,2,3

and I wish to retrieve


kid_id      kid_title    favorite_colors

1           Joe         yellow, eco-friendly, blue, crimson, red-colored

2           Mary        yellow, eco-friendly, blue

The queries are now being performed on existing data and database structure. If there's no achievable solution, ordering the dwelling from the information is possible however i assume would add a great deal of time for you to the answer. In either case, would really like outside assistance.

I can retrieve the colours in the database using:

SELECT STUFF((SELECT ', ' + color_name FROM colors WHERE color_id IN (1,2,3,4,5) FOR XML PATH('')),1, 2, '') AS colors

        colors
1       yellow, green, blue, purple, red

However when I attempt a far more complex query I can not appear to find away out to include the above mentioned statement by tugging the ids in the Kids table.

SELECT kids.kid_id, kids.kid_name, favorite_colors FROM kids JOIN colors ON colors.id IN kids.kid_favcolors as favorite_colors

Although it appears like it might try to me, no longer working. Unsure if I am way off or really close.

Within the original solution, storing CSV string inside a area violates 1NF really -- obviously -- the table doesn't even become qualified as a relation. So, within this situation treat the string being an atomic bit of data and decompose it around the application layer.

To resolve it around the DB layer, simply fix the look.

enter image description here

I've upset an answer climax not pretty, it really works.

SELECT 
  kid_id,
  kid_name, 
  (SELECT STUFF(
     (SELECT ', '+color_name FROM colors WHERE color_id in 
        (SELECT s from SplitString(kid_favcolors,',')) 
     FOR XML PATH(''))
   ,1,2,'')) AS favorite_colors 
FROM KIDS

kid_id      kid_title    favorite_colors

1           Joe         yellow, eco-friendly, blue, crimson, red-colored

2           Mary        yellow, eco-friendly, blue

I've spoken for them about reworking the DB and applying a present administration site to support the alterations

Split String function I made use of:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create function [dbo].[SplitString] 
(
    @str nvarchar(4000), 
    @separator char(1)
)
returns table
AS
return (
    with tokens(p, a, b) AS (
        select 
            1, 
            1, 
            charindex(@separator, @str)
        union all
        select
            p + 1, 
            b + 1, 
            charindex(@separator, @str, b + 1)
        from tokens
        where b > 0
    )
    select
        p-1 zeroBasedOccurance,
        substring(
            @str, 
            a, 
            case when b > 0 then b-a ELSE 4000 end) 
        AS s
    from tokens
  )