RE: Determining if query will work
Like so many other people..
No one puts any collective thought into what they are doing..
Instead of moving on and trying to finish the rest yourself, you rely
on someone else to finish it for you.
-Original Message-
From: Eve Atley [mailto:[EMAIL PROTECTED]
Sent: Monday, October 25, 2004 9:49 AM
To: [EMAIL PROTECTED]
Cc: Mysql
Subject: RE: Determining if query will work
I can't tell you how *glad* I am to get this running! Big hugs to you,
Shawn!
It's running beautifully. My only question is, after I run the queries,
I notice it won't let me create the temporary table again (saying
'tmpCandidates' already exists). Do I just need to then log out of my
client (MySQL Control Center) and back in to get rid of that temp table?
As I'll need to change what it searches for (ie baan, peoplesoft, etc.).
Or is there a query I can put in at the end of the queries to destroy
the temporary table once through with it?
Final query setup posted below.
Thanks,
Eve
CREATE TEMPORARY TABLE wow.tmpCandidates
SELECT DISTINCT r.Candidate_ID
FROM wow.resume r
WHERE r.Section_ID = '1'
AND MATCH (r.Section_Value) AGAINST ('+peoplesoft' IN BOOLEAN
MODE);
INSERT IGNORE INTO wow.resume_erp (Candidate_ID, Section_ID,
Section_Value)
SELECT SQL_CALC_FOUND_ROWS r.Candidate_ID, r.Section_ID,
r.Section_Value
FROM wow.tmpCandidates tc
INNER JOIN wow.resume r
on r.Candidate_ID = tc.Candidate_ID;
INSERT IGNORE 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,
c.Def_Rate_Unit, c.Other_Country, c.Currency_id,
c.Interview_Availability, c.Interview_Contact, c.US_Experience,
c.Location_Country
FROM wow.tmpCandidates tc
INNER JOIN wow.candidate c
ON c.Candidate_ID = tc.Candidate_ID;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Determining if query will work
Well that wasn't a very nice thing to say. On Mon, 2004-10-25 at 11:56, none none wrote: Like so many other people.. No one puts any collective thought into what they are doing.. Instead of moving on and trying to finish the rest yourself, you rely on someone else to finish it for you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Determining if query will work
I can't tell you how *glad* I am to get this running! Big hugs to you,
Shawn!
It's running beautifully. My only question is, after I run the queries,
I notice it won't let me create the temporary table again (saying
'tmpCandidates' already exists). Do I just need to then log out of my
client (MySQL Control Center) and back in to get rid of that temp table?
As I'll need to change what it searches for (ie baan, peoplesoft, etc.).
Or is there a query I can put in at the end of the queries to destroy
the temporary table once through with it?
Final query setup posted below.
Thanks,
Eve
CREATE TEMPORARY TABLE wow.tmpCandidates
SELECT DISTINCT r.Candidate_ID
FROM wow.resume r
WHERE r.Section_ID = '1'
AND MATCH (r.Section_Value) AGAINST ('+peoplesoft' IN BOOLEAN
MODE);
INSERT IGNORE INTO wow.resume_erp (Candidate_ID, Section_ID,
Section_Value)
SELECT SQL_CALC_FOUND_ROWS r.Candidate_ID, r.Section_ID,
r.Section_Value
FROM wow.tmpCandidates tc
INNER JOIN wow.resume r
on r.Candidate_ID = tc.Candidate_ID;
INSERT IGNORE 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,
c.Def_Rate_Unit, c.Other_Country, c.Currency_id,
c.Interview_Availability, c.Interview_Contact, c.US_Experience,
c.Location_Country
FROM wow.tmpCandidates tc
INNER JOIN wow.candidate c
ON c.Candidate_ID = tc.Candidate_ID;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Determining if query will work
I am so happy to get you working. Hopefully we helped some other along the
way ,too :-)
It's a good idea when working with data that you should always clean up
after yourself, regardless of what language you are using. Not only does
it free up resources faster it helps to make sure that you don't end up
with a contention issue like this.
I take the blame for this one! I gave you the CREATE TEMPORARY... without
the corresponding DROP TEMPORARY TABLE.
http://dev.mysql.com/doc/mysql/en/DROP_TABLE.html
Just put:
DROP TEMPORARY TABLE tmpCandidates
at the end of each pass and you will get rid of the temp table. No more
error message. Temp tables are connection specific so you don't have to
worry about more than one user/process sharing the same temp table, unless
they share the same database connection (connection pooling is one
example)
Come back to the list if you need any more help.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Eve Atley [EMAIL PROTECTED] wrote on 10/25/2004 12:48:43 PM:
I can't tell you how *glad* I am to get this running! Big hugs to you,
Shawn!
It's running beautifully. My only question is, after I run the queries,
I notice it won't let me create the temporary table again (saying
'tmpCandidates' already exists). Do I just need to then log out of my
client (MySQL Control Center) and back in to get rid of that temp table?
As I'll need to change what it searches for (ie baan, peoplesoft, etc.).
Or is there a query I can put in at the end of the queries to destroy
the temporary table once through with it?
Final query setup posted below.
Thanks,
Eve
CREATE TEMPORARY TABLE wow.tmpCandidates
SELECT DISTINCT r.Candidate_ID
FROM wow.resume r
WHERE r.Section_ID = '1'
AND MATCH (r.Section_Value) AGAINST ('+peoplesoft' IN BOOLEAN
MODE);
INSERT IGNORE INTO wow.resume_erp (Candidate_ID, Section_ID,
Section_Value)
SELECT SQL_CALC_FOUND_ROWS r.Candidate_ID, r.Section_ID,
r.Section_Value
FROM wow.tmpCandidates tc
INNER JOIN wow.resume r
on r.Candidate_ID = tc.Candidate_ID;
INSERT IGNORE 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,
c.Def_Rate_Unit, c.Other_Country, c.Currency_id,
c.Interview_Availability, c.Interview_Contact, c.US_Experience,
c.Location_Country
FROM wow.tmpCandidates tc
INNER JOIN wow.candidate c
ON c.Candidate_ID = tc.Candidate_ID;
RE: Determining if query will work
I think this will be my last question on the matter.
I was reading previous messages to the list regarding the boolean search
function, and its problems when searching with 3-letter search terms.
I'll need to search for sap as an ERP term. Is there an efficient way
to avoid getting something like 'sappy' when what I really want is just
'sap', nothing preceeding and nothing following?
Thanks,
Eve
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Monday, October 25, 2004 12:54 PM
To: [EMAIL PROTECTED]
Cc: Mysql
Subject: RE: Determining if query will work
I am so happy to get you working. Hopefully we helped some other along
the way ,too :-)
It's a good idea when working with data that you should always clean up
after yourself, regardless of what language you are using. Not only does
it free up resources faster it helps to make sure that you don't end up
with a contention issue like this.
I take the blame for this one! I gave you the CREATE TEMPORARY...
without the corresponding DROP TEMPORARY TABLE.
http://dev.mysql.com/doc/mysql/en/DROP_TABLE.html
Just put:
DROP TEMPORARY TABLE tmpCandidates
at the end of each pass and you will get rid of the temp table. No more
error message. Temp tables are connection specific so you don't have to
worry about more than one user/process sharing the same temp table,
unless they share the same database connection (connection pooling is
one example)
Come back to the list if you need any more help.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Eve Atley [EMAIL PROTECTED] wrote on 10/25/2004 12:48:43 PM:
I can't tell you how *glad* I am to get this running! Big hugs to you,
Shawn!
It's running beautifully. My only question is, after I run the
queries,
I notice it won't let me create the temporary table again (saying
'tmpCandidates' already exists). Do I just need to then log out of my
client (MySQL Control Center) and back in to get rid of that temp
table?
As I'll need to change what it searches for (ie baan, peoplesoft,
etc.).
Or is there a query I can put in at the end of the queries to destroy
the temporary table once through with it?
Final query setup posted below.
Thanks,
Eve
CREATE TEMPORARY TABLE wow.tmpCandidates
SELECT DISTINCT r.Candidate_ID
FROM wow.resume r
WHERE r.Section_ID = '1'
AND MATCH (r.Section_Value) AGAINST ('+peoplesoft' IN BOOLEAN
MODE);
INSERT IGNORE INTO wow.resume_erp (Candidate_ID, Section_ID,
Section_Value)
SELECT SQL_CALC_FOUND_ROWS r.Candidate_ID, r.Section_ID,
r.Section_Value
FROM wow.tmpCandidates tc
INNER JOIN wow.resume r
on r.Candidate_ID = tc.Candidate_ID;
INSERT IGNORE 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,
c.Def_Rate_Unit, c.Other_Country, c.Currency_id,
c.Interview_Availability, c.Interview_Contact, c.US_Experience,
c.Location_Country
FROM wow.tmpCandidates tc
INNER JOIN wow.candidate c
ON c.Candidate_ID = tc.Candidate_ID;
RE: Determining if query will work
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 fieldsSELECT 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
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
RE: Determining if query will work
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
RE: Determining if query will work
I think we're on to something. I've got one table importing data
correctly, resume to resume_erp. However, 'candidate' to 'candidate_erp'
appears goofy. I get the following error:
[mysql.loosefoot.com] ERROR 1062: Duplicate entry '1185' for key 1
With the following queries, query 3 being the problematic one:
# Connection: mysql.loosefoot.com
# Host: mysql.loosefoot.com
# Saved: 2004-10-22 14:51:15
#
CREATE TEMPORARY TABLE wow.tmpCandidates
SELECT DISTINCT r.Candidate_ID
FROM wow.resume r
WHERE r.Section_ID = '1'
AND MATCH (r.Section_Value) AGAINST ('+baan' 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.tmpCandidates tc
INNER JOIN wow.resume r
on r.Candidate_ID = tc.Candidate_ID
INNER JOIN wow.candidate c
ON c.Candidate_ID = tc.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,
c.Def_Rate_Unit, c.Other_Country, c.Currency_id,
c.Interview_Availability, c.Interview_Contact, c.US_Experience,
c.Location_Country
FROM wow.tmpCandidates tc
INNER JOIN wow.resume r
on r.candidate_ID = tc.Candidate_ID
INNER JOIN wow.candidate c
ON c.Candidate_ID = tc.Candidate_ID;
But I think we're almost there:)
Thanks,
Eve
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Determining if query will work
What is the definition of resume_erp (what is the result of: SHOW CREATE
TABLE resume_erp) ? What that error is telling you is that you are
attempting to add a record that matches a set of conditions that you said
could only exist once on the entire table (either your primary key or a
unique key). By looking at the definition of that table we can determine
where you should look for the duplication.
continued below...
Eve Atley [EMAIL PROTECTED] wrote on 10/22/2004 03:27:47 PM:
I think we're on to something. I've got one table importing data
correctly, resume to resume_erp. However, 'candidate' to 'candidate_erp'
appears goofy. I get the following error:
[mysql.loosefoot.com] ERROR 1062: Duplicate entry '1185' for key 1
With the following queries, query 3 being the problematic one:
# Connection: mysql.loosefoot.com
# Host: mysql.loosefoot.com
# Saved: 2004-10-22 14:51:15
#
CREATE TEMPORARY TABLE wow.tmpCandidates
SELECT DISTINCT r.Candidate_ID
FROM wow.resume r
WHERE r.Section_ID = '1'
AND MATCH (r.Section_Value) AGAINST ('+baan' 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.tmpCandidates tc
INNER JOIN wow.resume r
on r.Candidate_ID = tc.Candidate_ID
INNER JOIN wow.candidate c
ON c.Candidate_ID = tc.Candidate_ID;
The only reason you need to JOIN the table candidate to this query is to
make sure that you don't add any records to resume_erp unless they have a
corresponding record in candidate. If you are sure that all resumes still
have their corresponding candidate records then you can drop the second
INNER JOIN and just use the candidate_ID column from either resume or
tmpCandidates (your choice) in your SELECT clause.
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,
c.Def_Rate_Unit, c.Other_Country, c.Currency_id,
c.Interview_Availability, c.Interview_Contact, c.US_Experience,
c.Location_Country
FROM wow.tmpCandidates tc
INNER JOIN wow.resume r
on r.candidate_ID = tc.Candidate_ID
INNER JOIN wow.candidate c
ON c.Candidate_ID = tc.Candidate_ID;
But I think we're almost there:)
Yes, we are.
Thanks,
Eve
You're welcome,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
RE: Determining if query will work
What is the definition of resume_erp (what is the result of: SHOW CREATE TABLE resume_erp) ? What that error is telling you is that you are Glad you added more info, or I would have been asking you, 'what do you mean by definition?' :) CREATE TABLE `resume_erp` ( `Candidate_ID` int(10) NOT NULL default '0', `Section_ID` int(10) default NULL, `Section_Value` longtext ) TYPE=MyISAM DEFAULT CHARSET=latin1 And if you need candidate_erp...I had figured candidate_erp was the problem?... CREATE TABLE `candidate_erp` ( `Candidate_ID` int(11) NOT NULL auto_increment, `Vendor_ID` longtext, `Last_Name` longtext, `First_Name` longtext, `Middle_Initial` longtext, `Condition_Type` longtext, `Employer` longtext, `Country_ID` longtext, `Visa_Status` longtext, `Dt_Visa` longtext, `MMDD_Birth` longtext, `SSN` longtext, `CSG_Comments` longtext, `Working` longtext, `Available` longtext, `Start_Date` longtext, `Location` longtext, `HoldOnPeriod` longtext, `Relocation` longtext, `Tech_Ranking` longtext, `Comm_Ranking` longtext, `Availability` longtext, `Cert_Comments` longtext, `Dt_Submitted` longtext, `Def_Rate` longtext, `Def_Rate_Unit` longtext, `Other_Country` longtext, `Currency_id` longtext, `Interview_Availability` longtext, `Interview_Contact` longtext, `US_Experience` longtext, `Location_Country` longtext, PRIMARY KEY (`Candidate_ID`) ) TYPE=MyISAM DEFAULT CHARSET=latin1 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.tmpCandidates tc INNER JOIN wow.resume r on r.Candidate_ID = tc.Candidate_ID INNER JOIN wow.candidate c ON c.Candidate_ID = tc.Candidate_ID; The only reason you need to JOIN the table candidate to this query is to make sure that you don't add any records to resume_erp unless they have a corresponding record in candidate. If you are sure that all resumes still have their corresponding candidate records then you can drop the second INNER JOIN and just use the candidate_ID column from either resume or tmpCandidates (your choice) in your SELECT clause. Hmm, ok, thanks for this. Tells me I can safely drop te second INNER JOIN then. INSERT INTO wow.resume_erp (Candidate_ID, Section_ID, Section_Value) SELECT SQL_CALC_FOUND_ROWS c.Candidate_ID FROM wow.tmpCandidates tc INNER JOIN wow.resume r on r.Candidate_ID = tc.Candidate_ID; - Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Determining if query will work
I am attempting to copy data from 1 table into another, based on certain
criteria. I have set up the following queries, but am unsure if they
will function properly:
#This pulls back all data for matching candidates with keyword from
RESUME and CANDIDATE
SELECT * FROM wow.resume r INNER JOIN wow.candidate c WHERE r.Section_ID
= '1' AND MATCH (r.Section_Value) AGAINST ('+baan' IN BOOLEAN MODE) AND
c.Candidate_ID = r.Candidate_ID;
#This must copy all corresponding fields from RESUME into RESUME_ERP
*for each candidate ID previously found*
INSERT INTO wow.resume_erp
(Candidate_ID, Section_ID, Section_Value)
SELECT SQL_CALC_FOUND_ROWS *
FROM wow.resume r
INNER JOIN wow.candidate c;
#This must copy all corresponding fields from CANDIDATE into
CANDIDATE_ERP *for each candidate ID previously found*
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 *
FROM wow.resume r
INNER JOIN wow.candidate c;
Am I on the right track?
Please read further for the kind of data returned, if necessary.
Thanks,
Eve
--
Here's the kind of data returned...
| Candidate_ID | Section_ID | Section_Value
| 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 |
| 1185 | 1 |Information Technology
Strategic Systems Planning Development / Project Management / E-
Commerce / Technical Leadership/ Architectural Guidance/ Systems Design
Implementation / Organizational Structure
_
Management career encompasses a strong functional, technical, financial
and business acumen, including executive leadership in application
solutions, implementation, IT operations and teamwork. Experienced in
global business and information technology, ERP systems (MAPICS, BPCS,
JD Edwards, BAAN, ASK, Data 3. Having considerable experience and
expertise in the areas of distribution/logistics, materials management,
manufacturing, systems design and implementation, LAN/WANs, contract
negotiations and legal obligations, in addition to strategic business
planning.
SELECTED CAREER ACCOMPLISHMENTS
ó Designed and implemented a corporate-wide inventory and order
entry system that improved customer service and saved $70 million
dollars of inventory investment.
ó Automated internal operations through workflow/imaging systems
with an investment of $85,000 that saved $750,000 in annual engineering,
sales and accounting costs.
ó Instituted a technology leasing program that freed-up $8 million
in annual operating capital allowing for significant improvements in
infrastructure.
ó Implemented customer-focused manufacturing planning and
scheduling system that enabled sales growth of 70% in a world wide heavy
equipment machinery manufacturing company.
ó Consolidated contracts cutting cost for voice and data
networking services with annual savings of $200,000.
ó Re-implemented a failed ERP (BPCS) project with major gains in
on-time shipments, lower supply chain cost and improved management
control with an estimated value of almost $8 million dollars to the
corporation.
ó Created and marketed a successful educational game that taught
the basic principles of teamwork.
Systems experience includes: Enterprise Resource Planning (BPCS,
MAPICS), BPCS - Supply Chain Management, Human Resource Information
Systems (HRIS), CAD/CAM, Accounting and Financial reporting systems,
MRPII Class A certification, Configuration Management, Production
Scheduling, Forecasting, Inventory Management, Distribution (including
DRP), Purchasing, Warehousing, Imaging and Work Flow, and state of the
art manufacturing technologies, Process Flow, CIM, TQM. Sales Force
Automation and CRM.
| 1185 | 462 | Bishop | Jack|
| E | | 1 | N/A |
| | 123456 |
| 0 | 1 | 7/21/2003 | Lanchster PA| 24 hrs | No
| 5| 5| Immediate|
| 6/3/2003 | 90 | Hourly |
Determining if query will work
I am attempting to copy data from 1 table into another, based on certain
criteria. I have set up the following queries, but am unsure if they
will function properly:
#This pulls back all data for matching candidates with keyword from
RESUME and CANDIDATE
SELECT * FROM wow.resume r INNER JOIN wow.candidate c WHERE r.Section_ID
= '1' AND MATCH (r.Section_Value) AGAINST ('+baan' IN BOOLEAN MODE) AND
c.Candidate_ID = r.Candidate_ID;
#This must copy all corresponding fields from RESUME into RESUME_ERP
*for each candidate ID previously found*
INSERT INTO wow.resume_erp
(Candidate_ID, Section_ID, Section_Value)
SELECT SQL_CALC_FOUND_ROWS *
FROM wow.resume r
INNER JOIN wow.candidate c;
#This must copy all corresponding fields from CANDIDATE into
CANDIDATE_ERP *for each candidate ID previously found*
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 *
FROM wow.resume r
INNER JOIN wow.candidate c;
Am I on the right track?
Please read further for the kind of data returned, if necessary.
Thanks,
Eve
--
Here's the kind of data returned...
| Candidate_ID | Section_ID | Section_Value
| 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 |
| 1185 | 1 |Information Technology
Strategic Systems Planning Development / Project Management / E-
Commerce / Technical Leadership/ Architectural Guidance/ Systems Design
Implementation / Organizational Structure
_
Management career encompasses a strong functional, technical, financial
and business acumen, including executive leadership in application
solutions, implementation, IT operations and teamwork. Experienced in
global business and information technology, ERP systems (MAPICS, BPCS,
JD Edwards, BAAN, ASK, Data 3. Having considerable experience and
expertise in the areas of distribution/logistics, materials management,
manufacturing, systems design and implementation, LAN/WANs, contract
negotiations and legal obligations, in addition to strategic business
planning.
SELECTED CAREER ACCOMPLISHMENTS
ó Designed and implemented a corporate-wide inventory and order
entry system that improved customer service and saved $70 million
dollars of inventory investment.
ó Automated internal operations through workflow/imaging systems
with an investment of $85,000 that saved $750,000 in annual engineering,
sales and accounting costs.
ó Instituted a technology leasing program that freed-up $8 million
in annual operating capital allowing for significant improvements in
infrastructure.
ó Implemented customer-focused manufacturing planning and
scheduling system that enabled sales growth of 70% in a world wide heavy
equipment machinery manufacturing company.
ó Consolidated contracts cutting cost for voice and data
networking services with annual savings of $200,000.
ó Re-implemented a failed ERP (BPCS) project with major gains in
on-time shipments, lower supply chain cost and improved management
control with an estimated value of almost $8 million dollars to the
corporation.
ó Created and marketed a successful educational game that taught
the basic principles of teamwork.
Systems experience includes: Enterprise Resource Planning (BPCS,
MAPICS), BPCS - Supply Chain Management, Human Resource Information
Systems (HRIS), CAD/CAM, Accounting and Financial reporting systems,
MRPII Class A certification, Configuration Management, Production
Scheduling, Forecasting, Inventory Management, Distribution (including
DRP), Purchasing, Warehousing, Imaging and Work Flow, and state of the
art manufacturing technologies, Process Flow, CIM, TQM. Sales Force
Automation and CRM.
| 1185 | 462 | Bishop | Jack|
| E | | 1 | N/A |
| | 123456 |
| 0 | 1 | 7/21/2003 | Lanchster PA| 24 hrs | No
| 5| 5| Immediate|
| 6/3/2003 | 90 | Hourly |
Re: Determining if query will work
First a bit of friendly advice:
When performing an INSERT...SELECT... it is better to explicitly list the
source columns (part of the SELECT statement). Doing so will ensure that
if you re-order some columns or if you add/drop columns to/from one of the
tables participating in the SELECT portion of your statement that your
INSERT will still function. It is also the only way to perform an INSERT
so that an auto_increment column works properly. I know you end up with
longer statements but the statements are less likely to break and they are
easier to debug.
Eve Atley [EMAIL PROTECTED] wrote on 10/20/2004 01:31:59 PM:
I am attempting to copy data from 1 table into another, based on certain
criteria. I have set up the following queries, but am unsure if they
will function properly:
#This pulls back all data for matching candidates with keyword from
RESUME and CANDIDATE
SELECT * FROM wow.resume r INNER JOIN wow.candidate c WHERE r.Section_ID
= '1' AND MATCH (r.Section_Value) AGAINST ('+baan' IN BOOLEAN MODE) AND
c.Candidate_ID = r.Candidate_ID;
This query has a JOIN in it without an ON condition. Creating a JOIN in
this fashion relies on the optimizer to detect that you have a valid
table-to-table condition in your WHERE statement and apply it correctly.
If for some reason the optimizer misses this condition (c.Candidate_ID =
r.Candidate_ID) during the JOIN phase of the query (or if you neglected to
make one), you will produce a Cartesian product (every possible
combination of each row from both tables) of the tables you are joining
and will have to filter out all but the matching rows during the WHERE
phase of the query. I would re-write it this way:
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 ('+baan' IN BOOLEAN MODE);
#This must copy all corresponding fields from RESUME into RESUME_ERP
*for each candidate ID previously found*
INSERT INTO wow.resume_erp
(Candidate_ID, Section_ID, Section_Value)
SELECT SQL_CALC_FOUND_ROWS *
FROM wow.resume r
INNER JOIN wow.candidate c;
Here is an example of a INSERT...SELECT... that does not specify the
source columns. This is also an example of a Cartesian product. If you
have 12000 resumes and 1 candidates then the resume_erp table will end
up with 1200 (12 million) rows in it (12000 x 1). This is probably
NOT what you wanted. I would probably rewrite it this way
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;
I don't know all of your field names so I had to make up an ON clause.
Change the field names to match those actually in your system.
#This must copy all corresponding fields from CANDIDATE into
CANDIDATE_ERP *for each candidate ID previously found*
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 *
FROM wow.resume r
INNER JOIN wow.candidate c;
This is another example of a Cartesian product. This also another example
of a naked SELECT clause.
Specify your source columns (do not use SELECT SQL_CALC_FOUND_ROWS *)
and add an ON constraint to your INNER JOIN so that the appropriately
related rows are matched up.
Am I on the right track?
Yes, you were close. May I suggest a little reading?
http://dev.mysql.com/doc/mysql/en/JOIN.html
http://dev.mysql.com/doc/mysql/en/INSERT.html
http://dev.mysql.com/doc/mysql/en/INSERT_SELECT.html
Please read further for the kind of data returned, if necessary.
Thanks,
Eve
sample data snipped
RE: Determining if query will work
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.
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]
RE: Determining if query will work
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 fieldsSELECT 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
