List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:August 17 2004 7:14pm
Subject:Re: Assistance with SQL syntax: pulling duplicates back
View as plain text  
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
> 
> 
> 
> 

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