Posted by Hugo Kornelis on 04/17/07 22:34

On 17 Apr 2007 14:55:36 -0700, Chris wrote:

>I have a database column that stores a comma delimited list of foreign
>keys. Would someone show me how to do a join using the values from a
>list stored within a record?
>
>For example, a record in tbl_cds.genre_id might have a value of "2,
>5, 6" corresponding to genre_ids 2 , 5 and 6. I want to join
>tbl_cds.genre_id to tbl_genre.genre_id using the values in that data
>field.
>
>It seems I need a loop like this:
>SELECT * FROM tbl_cds
>WHERE
>Begin Loop
>tbl_cds.genre_id[i] = tbl_genre.genre_id
>End Loop.
>
>Would someone give me the correct syntax?
>Is there an alternative method that would create less overhead?
>
>Sorry for such a novice post.

Hi Chris,

You can't. And that's because the database design volates one of the
basic principles of databases - you store a single value in a column.

If a CD can belong to three genres, you'll have to add a table like
this:

CREATE TABLE CDGenres
(CD_ID int NOT NULL,
Genre_ID int NOT NULL,
PRIMARY KEY (CD_ID, Genre_ID),
FOREIGN KEY (CD_ID) REFERENCES tbl_cds(CD_ID),
FOREIGN KEY (Genre_ID) REFERENCES Genres(Genre_ID)
);

If you don't have a master table of all genres, then you can leave out
the last foreign key constraint.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

[Back to original message]