List:General Discussion« Previous MessageNext Message »
From:SGreen Date:August 17 2004 6:17pm
Subject:Re: Assistance with SQL syntax: pulling duplicates back
View as plain text  
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" <eatley@stripped> 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/mysql?unsub=1
> 

Thread
GUI for MySQLKirti S. Bajwa12 Aug
  • AW: GUI for MySQLFreddie Sorensen12 Aug
  • Re: GUI for MySQLKaram Chand13 Aug
  • Re: GUI for MySQLMartijn Tonies13 Aug
    • Full text for keyword(s) search?Eve Atley17 Aug
RE: GUI for MySQLGuillermo Chinchilla Zúñiga12 Aug
RE: Full text for keyword(s) search?Victor Pendleton17 Aug
  • Assistance with SQL syntax: pulling duplicates backEve Atley17 Aug
    • Re: Assistance with SQL syntax: pulling duplicates backMichael Stassen17 Aug
      • Re: Assistance with SQL syntax: pulling duplicates backMichael Stassen17 Aug
    • Re: Assistance with SQL syntax: pulling duplicates backSGreen17 Aug
RE: Assistance with SQL syntax: pulling duplicates backVictor Pendleton17 Aug
Re: Assistance with SQL syntax: pulling duplicates backMichael Stassen17 Aug