Verity Search Utility

How to search Multiple related tables

Page 1 of 1

5 Replies - 5156 Views - Last Post: 22 January 2006 - 11:48 PM Rate Topic: -----

#1 saini_boyz  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 28-November 05

Verity Search Utility

Posted 28 November 2005 - 02:08 PM

Hi People,
Please help me with my problem. I am working on a search utility with which I need to do a search on 5 related tables. I used JOIN to get the related records from these tables but dont get the desired results. TABLE descriptions are :
1) Resume
2) Education
3)Experience
4)Education Level
5) Clearance

Resume is the prime table. Education and Experience are related to it by ResumeId key. Education and Education Level are connected via EducationId. And Resume and Clearance are connected using ClearanceId. The search criteria that will be enterd by user can be anything that is present in these tables mentioned.

I tried JOIN to get the records and doing search on it but it did not work.
PLEASE HELP .
Thanks.

Is This A Good Question/Topic? 0
  • +

Replies To: Verity Search Utility

#2 skyhawk133  Icon User is offline

  • Head DIC Head
  • member icon

Reputation: 1877
  • View blog
  • Posts: 20,284
  • Joined: 17-March 01

Re: Verity Search Utility

Posted 28 November 2005 - 02:10 PM

Can you paste the SQL statement you're trying to use.
Was This Post Helpful? 0
  • +
  • -

#3 saini_boyz  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 28-November 05

Re: Verity Search Utility

Posted 28 November 2005 - 02:38 PM

Here it is....

<CFQUERY NAME="IndexResume" DATASOURCE="ResumeDatabase">

SELECT
RES.*,
CLE.vchClearance,
EXP.vchCompanyName ,
EXP.vchPosition ,
EXP.vchLocation ,
EXP.txtDescription ,
EDC.vchInstitution ,
EDC.vchMajor ,
EDLVL.vchEducationLevel

FROM
Resume RES
LEFT OUTER JOIN Clearance CLE ON(CLE.idnClearance = RES.idnClearance)
LEFT OUTER JOIN Experience EXP ON (EXP.idnResumeId = RES.idnResumeId)
LEFT OUTER JOIN Education EDC ON (EDC.idnResumeid=RES.idnResumeId)
LEFT OUTER JOIN EducationLevel EDLVL ON (EDLVL.idnEducationLevel=EDC.idnEducationLevel)

</CFQUERY>


<!--- Index the contents of the query object using CFINDEX --->
<CFLOCK NAME="CFINDEX_Lock" TYPE="EXCLUSIVE" TIMEOUT="30">
<CFINDEX COLLECTION="ResumeCollection" ACTION="Update" TITLE="vchLastName" QUERY="IndexResume" TYPE="Custom" BODY="idnResumeId,vchFirstName,vchLastName,txtExperienceSummary,txtTraining,txtCertification,txtSkills,txtMembership,txtAwards,vchClearance,IndexResume.vchInstitution,IndexResume.vchMajor,IndexResume.vchEducationLevel,IndexResume.vchCompanyName,vchPosition,txtDescription"
KEY="idnResumeId">
</CFLOCK>

<cfoutput>
<cfloop query="IndexResume">
#IndexResume.vchFirstName# #IndexResume.vchLastName# <p> #txtExperienceSummary#<p> #txtTraining#<p> #txtCertification# <p>#txtSkills# <p>#txtMembership#<p> #txtAwards#<p><!--- #IndexResume.vchClearance#<p>#vchCompanyName#<p> #vchPosition#<p>#vchInstitution#<p>#vchMajor#<p> <p>
#txtDescription#<p><hr>
</cfloop>

<CENTER>
<H2>Finished Indexing The Resume Database</H2>
</CENTER>
</cfoutput>
Was This Post Helpful? 0
  • +
  • -

#4 saini_boyz  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 28-November 05

Re: Verity Search Utility

Posted 29 November 2005 - 10:28 AM

ANYONE...ANY HELP...PLEASE
Was This Post Helpful? 0
  • +
  • -

#5 saini_boyz  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 28-November 05

Re: Verity Search Utility

Posted 01 December 2005 - 09:19 AM

Let me re-phrase my question...What changes I need to do in my Indexing code to get correct verity search results...My code for indexing the collection is pasted above...Thanks..
Was This Post Helpful? 0
  • +
  • -

#6 wiredwizard  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 0
  • View blog
  • Posts: 76
  • Joined: 07-May 05

Re: Verity Search Utility

Posted 22 January 2006 - 11:48 PM

first see if the query is bringing you back the correct results.

you might want to try a select as query so your cfindex doesnt need the scope titles

ie

<cfindex
collection = "ResumeCollection"
action = "Update"
type="CUSTOM"
title = "vchLastName"
key = "idnResumeId"
body ="idnResumeId,vchFirstName,
vchLastName,txtExperienceSummary,txtTraining,
txtCertification,txtSkills,txtMembership,
txtAwards,vchClearance,vchInstitution,
vchMajor,vchEducationLevel,vchCompanyName,
vchPosition,txtDescription"
query = "IndexResume">
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1