Eve,
From your earlier post, I see it should be
resume.Candidate_ID = candidate.Candidate_ID
===========
I should also point out that there are several problems with your Location matching. You have
candidate.Location LIKE '%CA%' OR 'California'
First, this evaluates as
(candidate.Location LIKE '%CA%') OR ('California')'California' evaluates as false, so only the first part can match. You probably meant
candidate.Location LIKE '%CA%' OR candidate.Location LIKE 'California'
In any case, '%CA%' matches 'California', so the latter part is still redundant. '%CA%' also matches 'Ocala', or any other string which contains 'ca'. I don't think that's what you want. Also, if the LIKE comparison string starts with a wildcard, an index on Location can't be used.
If candidate.Location contains only the state, then there is no need for the wildcards:
candidate.Location LIKE 'CA' OR candidate.Location LIKE 'California'
In fact, you wouldn't even need LIKE then:
candidate.Location IN ('CA', 'California')So, now your query would be
SELECT resume.Section_Value, candidate.Location
FROM resume JOIN candidate ON resume.Candidate_ID = candidate.Candidate_ID
WHERE resume.Section_ID = '1'
AND MATCH (resume.Section_Value) AGAINST ('html')
AND candidate.Location IN ('CA', 'California');You could improve this still further by changing all the states to the 2-letter form in your table and requiring the 2-letter state codes in the future. Then Location could be changed to the smaller, faster CHAR(2), and the last part of the WHERE clause would be
candidate.Location = 'CA'
On the other hand, if candidate.Location contains more than just the state, you're in trouble. It will be difficult to reliably separate rows which contain CA meaning California from rows which contain ca as part of something else. Possible, but difficult, and the solution will almost certainly prevent use of an index on Location.
Michael
Michael Stassen wrote:
You are joining two tables, resume and candidate. Without a join condition, you get a Cartesian product, each row of the first table paired with each and every row of the second table. (Some on this list would go so far as to say that's not even a join.) You need to specify how rows in resume should be lined up with rows in candidate. You are filtering the resulting rows with your WHERE conditions, but that's not the same thing.
I would expect that you have a relationship between resumes and candidates. One of them should have a column which holds a key with the ID value in the other. In the first case, you would add something like
resume.candidate_id = candidate.id
to your WHERE clause, and in the second case you would add something like
candidate.resume_id = resume.id
to your WHERE clause. I'd expect one candidate per resume, but possibly more than one resume per candidate, so I'd expect the first case.
Michael
Eve Atley wrote:
I think this is an easy question...I've set up a SQL statement like so:
SELECT resume.Section_Value, candidate.Location FROM resume, candidate WHERE
resume.Section_ID = '1' AND MATCH (resume.Section_Value) AGAINST ('html')
AND candidate.Location LIKE '%CA%' OR 'California'
------------------
And where 'html' should come up in 1 entry, I get duplicates when printing
out the field to the screen:
------------------
html unix network php Over 10 years of HTML experience. 2 years networking
administration.
html unix network php Over 10 years of HTML experience. 2 years networking
administration.
------------------
I can't decide if this is my code, or the SQL syntax. Would it be possible,
based on this statement, to have pulled back duplicates from the same
record?
- Eve
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
