OK, I'm going to break this down bit by bit
> 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.
Is there a huge list of skills? If not, do it via Checkboxes as multiple
selects really confuse people
> 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?
IN is effectively an OR, that's what it's there for... if you want to do an
AND it's going to be a lot more complicated
I can't think of a quick way to do this in SQL, it might be a mixture of CF
and SQL (this code is ugly, but it'll work
<cfquery name="myQuery" datasource="myDSN">
select e.empId,es.skillId
from emp e,empSkills es
where skillId IN (#frmSkill#)
AND e.empId=es.empId
order by e.empId,es.skillId
</cfquery>
<cfscript>
// Start with an array
empArray=ArrayNew(1);
// currently no person and skills
thisEmp=0;
thisEmpSkills="";
// loop through the data
for (i=1; i lte myQuery.RecordCount; i=i+1)
{// if we're not building for the current employee, add the last employee's
stuff into the array
if (myQuery.empID[i] neq thisEmp)
{// If it's not blank
if (thisEmp neq 0)
{// build it in a structure as it's easier to get info out of
tmpStruct=StructNew();
tmpStruct.empID=thisEmp;
tmpStruct.SkillList=thisEmpSkills;
// add it to the array
ArrayAppend(empArray,thisStruct);
}
// we're looking at "this" employee
thisEmp=myQuery.empID[i];
// start with blank skills
thisEmpSkills="";
}
// add the current skill to the list
thisEmpSkills=ListAppend(thisEmpSkills,myQuery.skillID[i]);
}
// Add the last person's entries to the array
tmpStruct=StructNew();
tmpStruct.empID=thisEmp;
tmpStruct.SkillList=thisEmpSkills;
ArrayAppend(empArray,thisStruct);
</cfscript>
You'll now have an array of the employees with their skills - so loop
through them and remove the ones who don't have all of the skills
As I say, ugly, but it should work
Philip Arnold
Director
Certified ColdFusion Developer
ASP Multimedia Limited
T: +44 (0)20 8680 1133
"Websites for the real world"
**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.
**********************************************************************
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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
