Hi Eve,
You have made a very common mistake while using the "comma-join" method. I
think if I translate your implicit inner join to an explicit inner join
you will spot your own mistake:
SELECT resume.Section_Value, candidate.Location
FROM resume
INNER JOIN candidate
WHERE resume.Section_ID = '1'
AND MATCH (resume.Section_Value) AGAINST ('html')
AND candidate.Location LIKE '%CA%' OR 'California'
You did not link your two tables. You didn't say that "this column in
resume matches up with this column in candidate" so the query engine put
together what is known as a Cartesian product. You are finding all of the
possible combinations of rows from both tables where your WHERE clause is
true.
You said you only get one row from "MATCH ... ('HTML')" (that's the
contribution from the resume table). However, you are getting two rows
from the candidate table based on "location like....". That's why you had
two rows in your results. Imagine if you had gotten 3 rows back from the
"Match..." clause... You would have had 6 records in your results and been
really confused, eh?
You can cure this by somehow equating the two tables. Depending on what
form of INNER JOIN you want to write you ether need another WHERE
condition or an ON clause.
FORM 1(I prefer this form):
SELECT resume.Section_Value, candidate.Location
FROM resume
INNER JOIN candidate
ON candidate.id = resume.candidate_id
WHERE resume.Section_ID = '1'
AND MATCH (resume.Section_Value) AGAINST ('html')
AND candidate.Location LIKE '%CA%' OR 'California'
FORM 2 (in comma-joined format):
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 candidate.id = resume.candidate_id
Like I said, it's a common mistake when you write your queries that way
(comma-join) to leave out the relationship condition.
Best Wishes,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Eve Atley" <[EMAIL PROTECTED]> wrote on 08/17/2004 01:22:45 PM:
>
> 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]
>
