Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2006
    Posts
    4

    Question Unanswered: 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!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Is this assignment posted anywhere that we can read it as the teacher orignally wrote it?

    -PatP

  3. #3
    Join Date
    Jan 2006
    Posts
    4
    This is for a web site project... Im stuck on this problem and need a solution. Thanks!

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jan 2006
    Posts
    4
    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!

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by L0Y4L1S3R
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •