Comma delimited list of IDs to a recordset from 2 tables...

Have 2 tables in SQL Server 05 DB:

First one is MyList

user_id -> unique value
list -> comma-delimited list of user_ids
notes -> random varchar data

Second one is MyProfile

user_id -> unique value
name
address
email
phone

I need a stored proc to return rows from MyProfile that match the comma-delimited contents in the "list" column of MyList, based on the user_id matched in MyList. The stored proc should receive as input a @.user_id for MyList then return all this data.

The format of the comma-delimited data is as such (all values are 10-digit alphanumerics):

d25ef46bp3,s46ji25tn9,p53fy76nc9

The data returned should be all the columns of MyProfile, and the columns of MyList (which will obviously be duplicated for each row returned).

Thank you!Is this assignment posted anywhere that we can read it as the teacher orignally wrote it?

-PatP|||This is for a web site project... Im stuck on this problem and need a solution. Thanks!|||Oh, Pat. I should HOPE this wasn't a homework assignment. I shudder to think that teachers would advise or condone storing data as comma-delimited strings...

L0Y4L1S3R, you can accomplish what you want joining with the LIKE() operator along with suitable wildcard characters on your string, but the result will be both inefficient and buggy. Complex coding is frequently required to make up for inadequecies in design, and ultimately you need to scrap the comma delimited strings and store that data in a subtable.|||Haha... definitely not school assignment... my first attempt at working with complex data in sql server 05.

Here is the situation. The MyList table stores this data in CD format because it can be one user_id or up to 100. So its either CD format format or 100 columns in the table.

Any way... if you can think for a stored proc that would work, please provide. Otherwise please recommend an alternate table structure.

thank you!|||Here is the situation. The MyList table stores this data in CD format because it can be one user_id or up to 100. So its either CD format format or 100 columns in the table.No no no no no no no. The correct "normalized" design is to have a subtable with up to 100 records per user_id.

Look, you can tell there is something fishy about your design because you have two table that each use user_id as a primary key. The proper design should probably be:

Table MyProfile
(user_id primary key,
name,
address,
email,
phone)

Table MyList
(user_id,
list_item,
notes)

In table MyList, user_id and list_time form a composite and unique primary key, and list_item stores one and only one item. This allows you to store as many list_items per user_id as you want, prevents a user_id from having duplicate list_items, and allows fast and easy querying.