subject:"RE\: Building Cold Fusion Search \- Dynamic SQL"

RE: Building Cold Fusion Search - Dynamic SQL

2001-02-27 Thread Christopher Olive, CIO


great googlymoogly!  my bad.  thanks.

(i've been doing "customer relationship" stuff all day.  must have rotted my
brain!)

chris olive, cio
cresco technologies
[EMAIL PROTECTED]
http://www.crescotech.com



-Original Message-
From: Maia, Eric [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, February 27, 2001 2:22 PM
To: CF-Talk
Subject: RE: Building Cold Fusion Search - Dynamic SQL


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

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
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:


 Skill1
 Skill2
 Skill3
 Skill4
(etcthis size will vary as it's dynamically populated)


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

RE: Building Cold Fusion Search - Dynamic SQL

2001-02-27 Thread Kim Mayhall


That's it!  Thank you so much for understanding my problem and giving me a
solution!

  
Kim Mayhall 
The Garrigan Lyman Group 
http://www.glg.com 
(206) 223-5548 


-Original Message-
From: Maia, Eric [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, February 27, 2001 11:22 AM
To: CF-Talk
Subject: RE: Building Cold Fusion Search - Dynamic SQL


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

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
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:


 Skill1
 Skill2
 Skill3
 Skill4
(etcthis size will vary as it's dynamically populated)


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

RE: Building Cold Fusion Search - Dynamic SQL

2001-02-27 Thread Maia, Eric


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

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
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:


 Skill1
 Skill2
 Skill3
 Skill4
(etcthis size will vary as it's dynamically populated)


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

RE: Building Cold Fusion Search - Dynamic SQL

2001-02-27 Thread Kim Mayhall


You know, I've tried this exact code, but it pulls up 0 records even though
I know there was at least one matching record.this confuses the heck out
of me.  A lot of people have mentioned the POST - I only put the snippet in
here to show you how I'm passing itmy problem is with actually writing
the query the right way. The action page is receiving the proper
valuesbut I can't get the recordset to return correctly.  
 
I was sure the code that Alistair posted would work, but it doesn't..
 

 


Kim Mayhall 
The Garrigan Lyman Group 
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

RE: Building Cold Fusion Search - Dynamic SQL

2001-02-27 Thread Christopher Olive, CIO


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
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:


 Skill1
 Skill2
 Skill3
 Skill4
(etcthis size will vary as it's dynamically populated)


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 
(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

Re: Building Cold Fusion Search - Dynamic SQL

2001-02-27 Thread Sean Renet


Phillip, her original code works, she just needs to make the form's method
"post"
- Original Message -
From: "Philip Arnold - ASP" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Tuesday, February 27, 2001 3:49 AM
Subject: RE: Building Cold Fusion Search - Dynamic SQL


> 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:
> >
> > 
> >  Skill1
> >  Skill2
> >  Skill3
> >  Skill4
> > (etcthis size will vary as it's dynamically populated)
> > 
> >
> > 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
>
> 
> 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
> 
> 
> // 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);
> 
>
> 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/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

RE: Building Cold Fusion Search - Dynamic SQL

2001-02-27 Thread alistair . davidson


or you could do 




SELECT e.empId,es.skillId
from emp e,empSkills es
where skillId = #SkillsClause#
AND e.empId=es.empId


So given  frmSkill = 1,3,4,7, ..etc

your query would say 

..
where skillID = 1 AND SkillID = 3 AND SkillID = 4 AND SkillID = 7 ..etc

That should do it

hope it helps

Alistair Davidson 
Senior Web Developer
Rocom
www.rocomx.net

-Original Message-
From: Philip Arnold - ASP [mailto:[EMAIL PROTECTED]]
Sent: 27 February 2001 11:49
To: CF-Talk
Subject: RE: Building Cold Fusion Search - Dynamic SQL


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:
>
> 
>  Skill1
>  Skill2
>  Skill3
>  Skill4
> (etcthis size will vary as it's dynamically populated)
> 
>
> 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


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


// 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);


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/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

RE: Building Cold Fusion Search - Dynamic SQL

2001-02-27 Thread Philip Arnold - ASP


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:
>
> 
>  Skill1
>  Skill2
>  Skill3
>  Skill4
> (etcthis size will vary as it's dynamically populated)
> 
>
> 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


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


// 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);


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/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists

Re: Building Cold Fusion Search - Dynamic SQL

2001-02-27 Thread Sean Renet


Hmmm, are you trimming the variables in the IN statement?  Is skillId set to
CHAR or VARCHAR?  If its CHAR you should check to make sure the values
themselves in the database don't have extra spaces.

If that's all correct, make sure your form's method is "post" and not "get".

That is,



- Original Message -
From: "Kim Mayhall" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Monday, February 26, 2001 11:23 AM
Subject: Building Cold Fusion Search - Dynamic SQL


> I'm trying to build some sql statements based on selections from a list
box:
>
> 
>  Skill1
>  Skill2
>  Skill3
>  Skill4
> (etcthis size will vary as it's dynamically populated)
> 
>
> 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 
> (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

Re: Building Cold Fusion Search - Dynamic SQL

2001-02-27 Thread han peng


hmm.. maybe u hav to set #frmSkill# as array...
then put each skill into different variables
at yr query..
use AND for each isdefined(variables.skill)

hope this help..

cheers
han
- 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:
>
> 
>  Skill1
>  Skill2
>  Skill3
>  Skill4
> (etcthis size will vary as it's dynamically populated)
> 
>
> 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 
> (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

RE: Building Cold Fusion Search - Dynamic SQL

RE: Building Cold Fusion Search - Dynamic SQL

RE: Building Cold Fusion Search - Dynamic SQL

RE: Building Cold Fusion Search - Dynamic SQL

RE: Building Cold Fusion Search - Dynamic SQL

Re: Building Cold Fusion Search - Dynamic SQL

RE: Building Cold Fusion Search - Dynamic SQL

RE: Building Cold Fusion Search - Dynamic SQL

Re: Building Cold Fusion Search - Dynamic SQL

Re: Building Cold Fusion Search - Dynamic SQL

10 matches

Site Navigation

Mail list logo

Footer information