I'll assume that using IN is not an option because each experience field
must be matched with it's corresponding years. That being said this query
is not easy. You need to seperate each form field with OR and use
parenthesis correctly. Try the following (it looks nasty, but trust me):
------------------------------------------
<cfset SQLstring = "">
<cfloop index="iName" list="#form.listintIndustryID#">
<cfset indName=Evaluate('form.indExp' & iName)>
<cfif indName neq "">
<cfset SQLstring = listAppend(SQLstring,"(ce.intIndustryIDFK
= #iName# AND ce.intCandIndYrs >= #indName#)")>
</cfif>
</cfloop>
<cfset SQLstring = replace(SQLstring,","," OR ","all")>
<cfquery datasource="#dataSource#" name="candInfo">
select c.*
from tblCandIndExp ce
LEFT OUTER JOIN
tblCandidates c ON
c.intCandidateID = ce.intCandidateIDFK
where 0=0
<CFIF form.intB2bSalesIDFK IS NOT "">
AND intB2bSalesIDFK #form.funcIntB2bSalesIDFK#
#form.intB2bSalesIDFK#
</CFIF>
<cfif SQLstring IS NOT "">
AND (#SQLstring#)
</cfif>
Order by #sortField# #sortOrder# #sortField2# #sortOrder2#
</cfquery>
-----------------------------------------------------------------
Bryan Love ACP
Internet Application Developer
[EMAIL PROTECTED]
-----Original Message-----
From: Stephen Kellogg [mailto:[EMAIL PROTECTED]]
Sent: Friday, May 25, 2001 11:31 AM
To: CF-Talk
Subject: To Join or not to join that is the question- and if so how
Hi all,
I hope everyone's having a great Friday.
I have several tables.
Table 1
intCandidateID
intB2bSalesIDFK
bunch of fields
Table 2
intIndID
intCandidateIDFK
industryIDFK
intCandIndYrs
I want to be able to search Table 1 for matching data AND table 2 for a
candidate with a specific industry and >= X number of years.
I am using the following code:
<cfquery datasource="#dataSource#" name="candInfo">
select tblCandidates.*
from {oj tblCandIndExp LEFT OUTER JOIN tblCandidates ON
tblCandidates.intCandidateID = tblCandIndExp.intCandidateIDFK}
where 0=0
<cfoutput>
<CFIF #form.intB2bSalesIDFK# IS NOT "">
AND (intB2bSalesIDFK #form.funcIntB2bSalesIDFK#
#form.intB2bSalesIDFK#)
</CFIF>
----<snip a bunch of cfif's>----
<cfloop index="iName" list=#form.listintIndustryID#>
<cfset indName=Evaluate('form.indExp' & iName)>
<cfif #indName# eq "">
<cfelse>
AND (tblCandIndExp.intIndustryIDFK = #iName#) AND
(tblCandIndExp.intCandIndYrs >= #indName#)
</cfif>
</cfloop>
Order by #sortField# #sortOrder# #sortField2# #sortOrder2#
</cfoutput>
</cfquery>
This actually works correctly if I put in information for table 1 and I only
put it one industry (represented as a number) and one number of years for
table 2.
However, and this is where I'm really confused, if I put in 2 industries and
2 number of years, it returns no records (even though I have verified the
data).
The query shows like this when I run the page.
SQL =
select tblCandidates.*
from {oj tblCandIndExp LEFT OUTER JOIN tblCandidates ON
tblCandidates.intCandidateID = tblCandIndExp.intCandidateIDFK}
where 0=0
AND (tblCandIndExp.intIndustryIDFK = 1) AND (tblCandIndExp.intCandIndYrs >=
2)
AND (tblCandIndExp.intIndustryIDFK = 15) AND (tblCandIndExp.intCandIndYrs >=
3)
Order by strStateAbrvIDFK ,intB2bSalesIDFK DESC
I think the problem is that it's looking for ONE record that has an
intIndustryIDFK of both 1 and 15 and an intCandIndYrs of both 2 and 3 which
of course doesn't exist so no records show.
How can I get around this?
Thanks for your help
Stephen Kellogg
[EMAIL PROTECTED]
Sales Recruitment Solutions, Inc.
www.mysalescareer.com
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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
