since a multiple select gives you nothing more than a list, try a list loop.
SELECT
e.empId,
es.skillId
FROM
emp e,
empSkills es
WHERE
e.empId=es.empId
<CFLOOP INDEX="skillidx" LIST="#FORM.frmSkill#)>
AND skillId = #frmSkill#
</CFLOOP>
chris olive, cio
cresco technologies
[EMAIL PROTECTED]
http://www.crescotech.com
-----Original Message-----
From: Kim Mayhall [mailto:[EMAIL PROTECTED]]
Sent: Monday, February 26, 2001 2:23 PM
To: CF-Talk
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/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
