Take the single quotes out.
Better still, use CFQP...
WHERE vols_volcat.VolCatID IN (<cfqueryparam value="#form.VolCatID#"
cfsqltype="CF_SQL_INTEGER" list="true"/>)
>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:269581
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
