I would simplify this search by breaking the query into two steps, just as
you described. First locate all of the resumes where section 1 contains
the bit of text you are looking for then use those results to get the full
resume (all 6 sections)
There are 3 ways to do this. One is a single-query method but it will
require a rather complex set of JOINS and I don't think the response from
it will be good enough (it should be quite slow, based on my experience),
one uses a subquery which your current version of MySQL may not support
(last I read, you were on 3.23.58), and the third which I show below uses
a TEMP TABLE:
CREATE TEMPORARY TABLE tmpCandidates
SELECT DISTINCT Candidate_ID
FROM resume
WHERE Section_ID = '1'
AND MATCH (r.Section_Value) AGAINST ('+peoplesoft' IN BOOLEAN
MODE);
SELECT SQL_CALC_FOUND_ROWS c.candidate_id, c.Vendor_ID, c.Last_Name,
c.First_Name, c.Middle_Initial, c.Condition_Type, c.Employer,
c.Country_ID, c.Visa_Status, c.Dt_Visa, c.MMDD_Birth, c.SSN,
c.CSG_Comments, c.Working, c.Available, c.Start_Date, c.Location,
c.HoldOnPeriod, c.Relocation, c.Tech_Ranking, c.Comm_Ranking,
c.Availability, c.Cert_Comments, c.Dt_Submitted, c.Def_Rate_Unit,
c.Other_Country, c.Currency_id, c.Interview_Availability,
c.Interview_Contact, c.US_Experience, c.Location_Country
FROM tmpCandidates tc
INNER JOIN wow.candidate c
ON tc.Candidate_ID = c.Candidate_ID;
If you had 200 resumes from 150 candidates where section 1 matched
"peoplesoft" and each resume had 6 sections, this will give you 150
candidate returns (you didn't get all 200 resume hits because I eliminated
the duplicate candidate matches with the DISTINCT keyword). If you want to
get each set of candidate information along with their resume information,
you will end up with all columns of the candidate information being
duplicated 6 times, once for each resume section. That query would look
like:
SELECT SQL_CALC_FOUND_ROWS c.candidate_id, c.Vendor_ID, c.Last_Name,
c.First_Name, c.Middle_Initial, c.Condition_Type, c.Employer,
c.Country_ID, c.Visa_Status, c.Dt_Visa, c.MMDD_Birth, c.SSN,
c.CSG_Comments, c.Working, c.Available, c.Start_Date, c.Location,
c.HoldOnPeriod, c.Relocation, c.Tech_Ranking, c.Comm_Ranking,
c.Availability, c.Cert_Comments, c.Dt_Submitted, c.Def_Rate_Unit,
c.Other_Country, c.Currency_id, c.Interview_Availability,
c.Interview_Contact, c.US_Experience, c.Location_Country, r2.section_id,
r2.section_value
FROM tmpCandidates tc
INNER JOIN resume r
on r.candidate_ID = tc.candidate_id
INNER JOIN wow.candidate c
ON c.Candidate_ID = tc.Candidate_ID;
This will return the full 1200 rows (200 resume matches x 6 sections per
resume) because we re-introduced (by joining to the Resume table) the
multiple resumes for each candidates.
Once you have tuned your select queries to return what you want, it's
trivially simple to add the INSERT (...) clause to the beginning of it to
get the results to go into a table instead of to your client.
Keep me informed on your progress, please. Thank you for being so patient.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Eve Atley" <[EMAIL PROTECTED]> wrote on 10/22/2004 12:43:26 PM:
>
> Sorry for the delay in this. Still having problems with this one.
>
> >The second query is going to produce one row for every resume. Based on
>
> >your results, I assume you have 1000 resumes that have candidates
> >associated with them.
>
> Let's say I have 256 resulting rows for table 'resume', Section_ID 1,
> where the word 'peoplesoft' was found. What I'm really trying to do is
> get the 256 that come up in the first query, and then work with only
> that 256. The way the table 'resume' is set up is with the following
> fields:
>
> Candidate_ID
> Section_ID
> Section_Value
>
> Section_ID 1 is where all basic text in a resume is stored; there are a
> total of 6 sections, each section holding something different. I must
> search first on Section 1 for references to 'peoplesoft'. Ultimately,
> each candidate will have 6 total sections in resume - in this case, 256
> x 6 = 1536. Therefore, I would need the second query to return 1536,
> returning results for all candidates matching for Section 1, then using
> the Candidate_ID to find all resulting rows in table 'resume'.
>
> >I also notice that ALL of the fields in your select statement come from
>
> >the candidate table. If that's truly the case, why do you need to JOIN
> to
> >the resume table?
>
> See above. :) Resume material is boolean-searched, then I have to copy
> that Candidate_ID from *both* tables 'candidate' and 'resume' into
> 'candidate_erp' and 'resume_erp'.
>
> Thanks,
> Eve
>
<previous responses snipped -- see list archives for details>
