Thanks for the help. If I'm reading this correctly, it will select someone
with experience in either industry but not ONLY people who match both.
industry = 10 and years >= 4 OR industry = 12 and years >= 8 They could have
one or the other but does not require someone to meet both. Is that correct?
My data looks like the following:
intIndID intCandidateIDFK industryIDFK intCandIndYrs
1 1001 10 4
2 1002 12 8
3 1003 10 4
4 1009 2 7
5 1001 12 8
With this data set, I would want to retrieve candidate 1001 because that
candidate meets both industry = 10 and years >= 4 AND industry = 12 and
years >= 8.
I would not want candidates 1002 or 1003 because they only match 1 of the
sets.
Did I read your code correctly? Would it select 1001 twice and 1002 and 1003
once? I want the candidate to have to have both.
Thanks again
Stephen
-----Original Message-----
From: Bryan Love [mailto:[EMAIL PROTECTED]]
Sent: Friday, May 25, 2001 3:02 PM
To: CF-Talk
Subject: RE: To Join or not to join that is the question- and if so how
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
