Professional Web Applications Themes

JOIN using LIKE - MySQL

I have to two tables, here's the structure table1 - First Name - Last Name - Group Name - IDNumber table 2 - Name (contains first and last) - Group Name - IDNumber Table 2 has complete information while table1 is incomplete and does not have the IDnumber for all records. My goal is to determine which records in table1 do not have the ID numbers that are present in table 2 I tried to make this comparison using by checking if table2.NAME like table1.FirstName or table1.Lastname I tried to use the JOIN statement on condition table2.NAME like concat('%',table1.FirstName,'%') OR ...

  1. #1

    Default JOIN using LIKE

    I have to two tables, here's the structure

    table1
    - First Name
    - Last Name
    - Group Name
    - IDNumber


    table 2
    - Name (contains first and last)
    - Group Name
    - IDNumber


    Table 2 has complete information while table1 is incomplete and does
    not have the IDnumber for all records. My goal is to determine which
    records in table1 do not have the ID numbers that are present in table
    2

    I tried to make this comparison using by checking if table2.NAME like
    table1.FirstName or table1.Lastname

    I tried to use the JOIN statement on condition table2.NAME like
    concat('%',table1.FirstName,'%') OR table2.NAME like
    concat('%',table1.LastName,'%')

    The total number of records I have in table2 is 635 and the total
    number of records in table1 is 840. however, when I run the query, I
    get 3650 rows returned with repeated values...

    I am not sure how to establish this logic in mySQL. Could someone
    please help?

    Thanks for your time..

    -HJ

    HyperJiver Guest

  2. #2

    Default Re: JOIN using LIKE

    HyperJiver wrote: 

    SELECT * FROM table1
    LEFT JOIN table2 USING(IDNumber)
    where table2.IDNumber IS NULL


    Paul Guest

  3. #3

    Default Re: JOIN using LIKE

    Sorry,

    I forgot to add that, I need to update table1 to include the ID
    numbers. I can find the mismatches, How do I do an update?

    Thanks


    Paul Lautman wrote: 
    >
    > SELECT * FROM table1
    > LEFT JOIN table2 USING(IDNumber)
    > where table2.IDNumber IS NULL[/ref]

    HyperJiver Guest

  4. #4

    Default Re: JOIN using LIKE

    HyperJiver wrote: 

    You can do multi-table updates in MySQL 4.0.4 and later.

    Here are a couple of examples:

    UPDATE table1 AS t1 JOIN table2 AS t2
    ON t2.name = CONCAT(t1.firstName, ' ', t1.lastName)
    AND t1.groupName = t2.groupName
    SET t1.idNumber = t2.idNumber
    WHERE t2.idNumber is null OR t2.idNumber != t1.idNumber;

    The above assumes that t2.name is exactly the concatenation of the first
    and last names from t1, with a space between. If not, you may need to
    use LIKE:

    UPDATE table1 AS t1 JOIN table2 AS t2
    ON t2.name LIKE CONCAT(t1.firstName, '%')
    AND t2.name LIKE CONCAT('%', t1.lastName)
    AND t1.groupName = t2.groupName
    SET t1.idNumber = t2.idNumber
    WHERE t2.idNumber is null OR t2.idNumber != t1.idNumber;

    I'm also assuming in both examples that the groupName column is correct
    and complete in both tables, and can be used as an additional join
    condition.

    Regards,
    Bill K.
    Bill Guest

Similar Threads

  1. Is left-join faster then inner join?
    By howachen@gmail.com in forum MySQL
    Replies: 5
    Last Post: March 5th, 07:11 AM
  2. SQL join
    By sikhar in forum Coldfusion Database Access
    Replies: 15
    Last Post: April 20th, 04:21 PM
  3. Replies: 2
    Last Post: September 18th, 09:59 PM
  4. DB2 join
    By Prakash in forum IBM DB2
    Replies: 1
    Last Post: September 10th, 05:24 PM
  5. Select Left Join AND Right Join
    By David in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 15th, 03:42 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139