#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2000
    Posts
    35
    Rep Power
    14

    Select statement for similar name in another table...


    Hello,

    I've got the following in the table scores:

    id, name, score

    In the table ftf I've got the following:

    ID, Name, Level, Score

    In table scores there is a value for the 'name' field of "Timothy John Mousel". In table 'ftf' there is a value for the 'Name' field of "Mousel,Timothy John".

    How would I write a query to find a name in one table that matches the name in the other table.

    The query below doesn't return the result but it's hopefully somewhat close to what I want to do.

    UPDATE 'ftf' SET ftf.Score = scores.score WHERE ftf.Name LIKE '%name%'

    Thanks in advance,

    Tim
  2. #2
  3. Put a potato on it!
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2008
    Location
    Maryland
    Posts
    304
    Rep Power
    971
    Originally Posted by Bigtime
    Hello,

    I've got the following in the table scores:

    id, name, score

    In the table ftf I've got the following:

    ID, Name, Level, Score

    In table scores there is a value for the 'name' field of "Carla Andrea Claros". In table 'ftf' there is a value for the 'Name' field of "Claros,Carla Andrea".

    How would I write a query to find a name in one table that matches the name in the other table.

    The query below doesn't return the result but it's hopefully somewhat close to what I want to do.

    UPDATE 'ftf' SET ftf.Score = scores.score WHERE ftf.Name LIKE '%name%'

    Thanks in advance,

    Tim
    In ftf are the names always formatted as last name, first name? You could then do

    PHP Code:
    UPDATE 'ftf' SET ftf.Score scores.score WHERE ftf.Name LIKE '%last name%,%first name%' 
    "Those who can make you believe absurdities can make you commit atrocities."
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,203
    Rep Power
    4279
    Code:
    UPDATE ftf
    INNER
      JOIN scores
        ON scores.name = CONCAT(SUBSTRING_INDEX(ftf.name,',',-1)
                               ,' '
                               ,SUBSTRING_INDEX(ftf.name,',',1))
       SET ftf.score = scores.score
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2000
    Posts
    35
    Rep Power
    14
    Thanks guys.

    In the scores table the data could be any of the following formats:

    Timothy John Mousel
    Tim John Mousel
    Timothy Mousel
    Tim Mousel

    Any ideas on how it could be accomplished using these possible formats?

    Thanks,

    Tim
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,203
    Rep Power
    4279
    clean up your data, and not with sql -- buy a professional name/address cleansing product
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo