Kim,
The input from the multiple select will be the 2 (or more) skills selected
"skill1,skill2".
Since this is a list, why not loop thru it to construct dynamic sql that
ANDs the skills:
<cfquery .....>
SELECT e.empId,es.skillId
from emp e,empSkills es
where e.empId=es.empId
<cfloop index="i" list="#form.frmSkill#">
AND skilld = '#i#'
<cfloop>
</cfquery>
That will end up being
AND skilld='skill1'
AND skilld='skill2'
which is what you want.
Sheila Handler
jdt technologies, ltd
[EMAIL PROTECTED]
----- Original Message -----
From: Kim Mayhall <[EMAIL PROTECTED]>
To: CF-Talk <[EMAIL PROTECTED]>
Sent: Tuesday, February 27, 2001 3:23 AM
Subject: Building Cold Fusion Search - Dynamic SQL
> I'm trying to build some sql statements based on selections from a list
box:
>
> <select name="frmSkill">
> <option> Skill1
> <option> Skill2
> <option> Skill3
> <option> Skill4
> (etc....this size will vary as it's dynamically populated)
> </select>
>
> A user can do a multiple select, so let's say the user picked option 1 and
> option 3.
>
> On my action page, I want to pick ONLY the employees that have the 2
skills
> selected.
>
> If I use an IN statement, I get employees with one skill or the other, but
I
> only want those with both:
>
> SELECT e.empId,es.skillId
> from emp e,empSkills es
> where skillId IN (#frmSkill#)
> AND e.empId=es.empId
>
> How can I write the SQL to select only the employees with both, or do I
use
> Cold Fusion to do that?
>
> Kim Mayhall
> The Garrigan Lyman Group
> http://www.glg.com <http://www.glg.com/>
> (206) 223-5548
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
