For instance...
Select *
>From usergroups
left join users on usergroups.userID = users.userID
left join groups on usergroups.groupid = groups.groupID
Where usergroups.userID = 2
This would return a record set like:
UserID GroupID UserName GroupName
2 1 Daniel Kessler FamilyStudies
2 2 Daniel Kessler Kinnesiology
Then you can use the <cfoutput> group attribute to group by user ID so
you don't output multiple names for each user but only output the name
once but output each groupname individually. You may want to read up on
SQL joins as well as CFOUTPUT a little more but it will definitely make
life easier.
John
-----Original Message-----
From: daniel kessler [mailto:[EMAIL PROTECTED]
Sent: Wednesday, July 21, 2004 3:30 PM
To: CF-Talk
Subject: Re: DB field setup
Yeah, I believe it does make sense, and I think if my head doesn't
explode within 10 minutes, I'll probably last the night.
So anytime I have a list of anything within a list when I don't want to
do "a|b|c", I go to a lookup table and abstract (if that's the right
term) both sets. What I was missing mentally is having multiple entries
for a person in the user/groups section.
I guess this means that I can't just use CF Query="" to root through a
list and have to do my own looping to up the loop numbers through vars
with multiple people entries.
>No, I'm suggesting the following:
>
>GROUPS
>GroupID GroupName
>1 Family Studies
>2 Kinnesiology
>
>USERS
>UserID UserName
>1 John Burns
>2 Daniel Kessler
>
>USERGROUPS
>UserID GroupID
>1 1
>2 1
>2 2
>
>Which would mean that John Burns is in Family Studies while Daniel
>Kessler is in Family Studies and Kinnesiology. I hope that helps.
>
>John Burns
>
>-----Original Message-----
>From: daniel kessler [mailto:[EMAIL PROTECTED]
>Sent: Wednesday, July 21, 2004 2:22 PM
>To: CF-Talk
>Subject: Re: DB field setup
>
>>Seems like you need a lookup table (in case the possibilities ever
>>increase from 3 to X). Then you can either store the IDs in a
>>comma-delimited list or do another lookup table where you insert the
>>userID and the type they are. Then each user can be in multiple
>places.
>
>While I agree that I probably need a lookup table, I don't really
>understand your suggestions. Are you suggesting a Family_Studies
>tables with a set of names? And would I do an INNER JOIN to grab that
>information?
>
>I'm a bit new to DBs.
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
