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 -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 20, 2004 3:50 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: Determining if query will work > SELECT * > FROM wow.resume r > INNER JOIN wow.candidate c > ON c.Candidate_ID = r.Candidate_ID > WHERE r.Section_ID = '1' > AND MATCH (r.Section_Value) AGAINST ('+peoplesoft' IN BOOLEAN > MODE); > > INSERT INTO wow.resume_erp (Candidate_ID, Section_ID, Section_Value) > SELECT SQL_CALC_FOUND_ROWS c.candidate_id, r.Section_ID, r.Section_Value > FROM wow.resume r > INNER JOIN wow.candidate c > ON r.Candidate_ID = c.Candidate_ID; > > INSERT INTO wow.candidate_erp (Candidate_ID, Vendor_ID, Last_Name, > First_Name, Middle_Initial, Condition_Type, Employer, Country_ID, > Visa_Status, Dt_Visa, MMDD_Birth, SSN, CSG_Comments, Working, > Available, Start_Date, Location, HoldOnPeriod, Relocation, > Tech_Ranking, Comm_Ranking, Availability, Cert_Comments, Dt_Submitted, > Def_Rate, Def_Rate_Unit, Other_Country, Currency_id, > Interview_Availability, Interview_Contact, US_Experience, > Location_Country) 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 wow.resume r INNER JOIN wow.candidate c > ON r.Candidate_ID = c.Candidate_ID; > > I changed the boolean search to what I was matching against (this time > 'peoplesoft') with a resulting 256 rows. > > I ran the first two queries, which resulted in part 1 returning 256 > rows, part 2 affecting 1000 rows, and nothing entering into the > database candidate_erp. The third resulted in ERROR 1136: Column count > doesn't match value count at row 1. 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. In the third query you left out a field (c.def_rate, perhaps?). Here are your fields lined up vertically INSERT fields SELECT fields Candidate_ID, c.candidate_id, Vendor_ID, c.Vendor_ID, Last_Name, c.Last_Name, First_Name, c.First_Name, Middle_Initial, c.Middle_Initial, Condition_Type, c.Condition_Type, Employer, c.Employer, Country_ID, c.Country_ID, Visa_Status, c.Visa_Status, Dt_Visa, c.Dt_Visa, MMDD_Birth, c.MMDD_Birth, SSN, c.SSN, CSG_Comments, c.CSG_Comments, Working, c.Working, Available, c.Available, Start_Date, c.Start_Date, Location, c.Location, HoldOnPeriod, c.HoldOnPeriod, Relocation, c.Relocation, Tech_Ranking, c.Tech_Ranking, Comm_Ranking, c.Comm_Ranking, Availability, c.Availability, Cert_Comments, c.Cert_Comments, Dt_Submitted, c.Dt_Submitted, Def_Rate, Def_Rate_Unit, c.Def_Rate_Unit, Other_Country, c.Other_Country, Currency_id, c.Currency_id, Interview_Availability, c.Interview_Availability, Interview_Contact, c.Interview_Contact, US_Experience, c.US_Experience, Location_Country c.Location_Country 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? I would just change your FROM clause to read: FROM wow.candidate c This way you don't get 1000 records in your candidates_erp table when you don't have that many to start with. The duplicates would be caused by the JOIN to resume because I know that some of your candidates have more than one resume. You are doing great so far. You are almost there. Shawn Green Database Administrator Unimin Corporation - Spruce Pine > > > >Yes, you were close. May I suggest a little reading? > > I'll give those links a look, though often the reading in there seems > a bit thick! > > - Eve > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
