The problem with this approach is that it ignores the relational model of
the two tables. Each record in empSkills will only have one value for
skillId, so you won't get any records looking for two values. You'll need to
do a self-join or subquery to look for one record in emp that has two or
more related records in empSkills that match the two skills you're looking
for.
Try this:
SELECT
e.empId,
es.skillId
FROM
emp e,
empSkills es
WHERE
e.empId=es.empId
<CFLOOP INDEX="skillidx" LIST="#FORM.frmSkill#)>
AND e.empID IN
(SELECT empID
FROM empSkills
WHERE skillId = #skillidx#)
</CFLOOP>
GROUP BY empId
-----Original Message-----
From: Christopher Olive, CIO [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, February 27, 2001 6:43 AM
To: CF-Talk
Subject: RE: Building Cold Fusion Search - Dynamic SQL
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/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
