|
|
Posted by Erland Sommarskog on 04/18/07 22:22
Chris (christopher.b.lewis@gmail.com) writes:
> 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?
As Hugo said, you are in desperate need of a redesign.
On SQL 2005 you can nevertheless do:
SELECT *
FROM tbl_cds c
OUTER APPLY list_to_table(c.genre_id) l
JOIN tbl_genre g ON l.num = g.genre_id
where list_to_table is a table-valued function that unpacks the
comma-separated list into a table. See
http://www.sommarskog.se/arrays-in-sql.html for examples of such functions.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|