Comma Seperation List and Join Statement

[Flashm@n]

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]

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

"Flashm@n" <webforumsuser@macromedia.com> wrote in message
news:d61pam$sot$1@forums.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
>

[philh]

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,