Howdy,
I'm having a bit of a brain fart trying to figure this out. I want to
join four tables to find a user/volunteer that is assigned to
categories, pulling out all categories they are in based on a list of
IDs coming from a form submission.
Here's what my tables look like:
vols_volcat
===========
VolCatID (smallint)
VolunteerID (smallint)
volcategories
=============
VolCatID (smallint)
VolCategory (varchar)
volunteers
==========
VolunteerID (smallint)
UserID (smallint)
users
=====
UserID (smallint)
FName (varchar)
Lname (varchar)
The formfield being passed into the action page will contain one or more
IDs from a multiple select list (in my test case, form.VolCatID =
6,7,15,16).
Here's two queries that I've tried. There is one volunteer listed in the
vols_volcat table (volunteerID = 4) that matches all four IDs in
form.VolCatID passed in, but the query is returning only the first match
for VolCatID ("6"). I suspect the problem is that I'm using the "IN"
clause in the WHERE statement (where it stops after matching the first
number in the list). I need to grab all VolCategory fields the volunteer
matches.
1st query:
SELECT
volcategories.volcategory,
volunteers.volunteerID,
users.userid,
users.fname,
users.lname
FROM
volcategories
Inner Join vols_volcat ON vols_volcat.VolCatID = volcategories.VolCatID
Inner Join volunteers ON volunteers.VolunteerID = vols_volcat.VolunteerID
Inner Join users ON volunteers.UserID = users.UserID
WHERE vols_volcat.VolCatID IN ('#form.VolCatID#')
2nd query:
select
volcategories.volcategory,
volunteers.volunteerID,
users.userid,
users.fname,
users.lname
from
vols_volcat, volcategories, volunteers, users
where
vols_volcat.volcatID = volcategories.volcatID AND
vols_volcat.volunteerID = volunteers.volunteerID AND volunteers.userID =
users.userID AND vols_volcat.volcatID IN ('#form.VolCatID#')
I'm thinking there needs to be a loop somewhere in there to loop over
the form.VolCatID list being passed in, but can't get my head around it.
Any help would be appreciated.
Using CFMX 7 on local Windows dev box and MySQL 5.x
Thanks.
--
Chris Montgomery
Airtight Web Services http://www.airtightweb.com
Web Development, Web Project Management
210-490-2415 (office), 210-232-2790 (mobile)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Upgrade to Adobe ColdFusion MX7
Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs
http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:269578
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
