Incidentally, we are using a newer 4 version of MySQL now, if there's another way apart from a temp table to do this?
Let me try with the temp table meanwhile. Thanks! -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, October 22, 2004 1:27 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: Determining if query will work 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> -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
