See embedded comments ........
"Eve Atley" <[EMAIL PROTECTED]> wrote on 10/20/2004 03:33:41 PM:
>
> Based on Shawn's tips, I revised the query to read thusly:
>
> 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
>
>
>
>
>
