Professional Web Applications Themes

Comma Seperation List and Join Statement - Coldfusion Database Access

Hi I have a table with a field that contains a comma sperated list of userID's (e.g c43, c45, c48). I'm trying to join the users table with this table to pull out the firstname and surname of each user in the list. If the list only contains one user (eg C43) the join statement works perfectly and the user details are return and outputted via coldfusion. The problem I have is outputing the users details when there are multiple user id's in the field. I thought I could use the cfquerypara and specifie the field as a list but ...

  1. #1

    Default Comma Seperation List and Join Statement

    Hi

    I have a table with a field that contains a comma sperated list of userID's
    (e.g c43, c45, c48). I'm trying to join the users table with this table to
    pull out the firstname and surname of each user in the list. If the list only
    contains one user (eg C43) the join statement works perfectly and the user
    details are return and outputted via coldfusion.

    The problem I have is outputing the users details when there are multiple user
    id's in the field. I thought I could use the cfquerypara and specifie the
    field as a list but either I have not set this up correctly or I am not
    outputting the result properly.

    Does anyone have a solution?

    Thanks

    Mark

    Flashm@n Guest

  2. #2

    Default Re: Comma Seperation List and Join Statement

    If I understand your question, then something like

    select
    FirstName,
    LastName
    from
    yourTable
    where
    userid in (yourList)

    where yourList is (c43, c45, c48). You may or may not need
    userid in (#yourList#)

    hth

    -brian

    "Flashmn" <webforumsusermacromedia.com> wrote in message
    news:d61pam$sot$1forums.macromedia.com...
    > Hi
    >
    > I have a table with a field that contains a comma sperated list of
    userID's
    > (e.g c43, c45, c48). I'm trying to join the users table with this table
    to
    > pull out the firstname and surname of each user in the list. If the list
    only
    > contains one user (eg C43) the join statement works perfectly and the user
    > details are return and outputted via coldfusion.
    >
    > The problem I have is outputing the users details when there are multiple
    user
    > id's in the field. I thought I could use the cfquerypara and specifie the
    > field as a list but either I have not set this up correctly or I am not
    > outputting the result properly.
    >
    > Does anyone have a solution?
    >
    > Thanks
    >
    > Mark
    >

    Brian Hogue Guest

  3. #3

    Default Re: Comma Seperation List and Join Statement

    This is a common error. You can't put related keys in a comma-separated list
    in a field and expect them to represent a relationship. That's what RDBMS is
    for, and what relational design is for.

    You need what's called a "many-to-many" relationship, i.e., many students can
    be related to more than one record in your target table.

    Consider this structure:

    USERS
    ID
    firstname
    lastname

    DETAILS
    ID
    desc
    (etc.)

    USERSDETAILS
    ID
    user_fk
    details_fk

    That comma-delimited list you have should be broken up and placed in the
    USERSDETAILS table. Then you can do correct relational queries:

    SELECT a.firstname,a.lastname, c.desc FROM DETAILS
    JOIN USERSDETAILS b on b.details_fk = a.ID
    JOIN USERS c on c.ID = b.user_fk

    And, to get just one, add

    WHERE c.ID = #thedetailprimarykey#

    HTH,

    philh Guest

Similar Threads

  1. Noob Ques Comma delimited list
    By Winston2 in forum Coldfusion - Getting Started
    Replies: 3
    Last Post: June 22nd, 07:00 PM
  2. Comma displays in List?
    By sue_mmcxii in forum Macromedia Flash Data Integration
    Replies: 0
    Last Post: February 22nd, 12:35 AM
  3. Need help with SQL statement for JOIN-type stuff
    By Mike in forum ASP Database
    Replies: 13
    Last Post: August 16th, 10:36 AM
  4. comma delimited list problem
    By Shaun Perry in forum Macromedia Flash Actionscript
    Replies: 1
    Last Post: February 16th, 12:09 PM
  5. Splitting Comma delimited list
    By Colin Johnstone in forum PERL Beginners
    Replies: 10
    Last Post: December 30th, 04:01 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139