Ask Experts Questions for FREE Help !
    psychlopes's Avatar
    psychlopes Posts: 3, Reputation: 1
    New Member

    Oct 27, 2008, 08:22 PM
    Joining tables (Not an easy one)

    I have two tables that I want to join by means of matching a telephone number from the first table with the corresponding country/area code prefix in the second, the only problem is that I can't simply use "like" because more than one prefix might match the phone number, but I want only to only join to the matching prefix with the most number of digits. Below is an example:

    Phone number in table 1:

    Matching prefixes in table 2 may be as follows:

    In this example I want to join the record with that phone number with the record for the prefix 9873.. . I hope that makes sense. Oh and there's a big catch, it has to be done without grouping, so a MAX(CHAR_LENGTH()) kind of solution is not an option for me.

    I've found a sort of work around for this, but it is way too slow to consider as anything but a last resort. I know there must be an easy way to accomplish this, but I guess I'm just still too new to MySQL to see it.

    Best Regards
    jstrike's Avatar
    jstrike Posts: 418, Reputation: 44
    Full Member

    Jan 7, 2009, 12:15 PM
    You might be able to use a case statement in the join.

    Select... whatever...
    from table1 as a
    join table2 as b on a.field1 = case when <condition> then b.field1 when<condition> then b.field2 end
    Modav81's Avatar
    Modav81 Posts: 1, Reputation: 1
    New Member

    Jan 9, 2010, 12:39 AM
    select y.*,z.*
    (select a.*
    from tbl1 a) y
    left join
    (select b.prefix
    from table2 b) z on SUBSTRING(,1,LEN(z.PREFIX)) = z.PREFIX
    crigby's Avatar
    crigby Posts: 4,343, Reputation: 107
    Outdoor Power Equipment Expert

    Jan 9, 2010, 08:29 AM

    I pretty much agree with the above answer, but could not give these links in a Rating. I believe you want a left join.
    MySQL Table Joins
    MySQL :: MySQL 5.0 Reference Manual :: JOIN Syntax

Not your question? Ask your question View similar questions


Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.

Check out some similar questions!

Anuitty tables [ 6 Answers ]

Can anyone explain annuitties in lay mans terms.

Help with Multiplication tables [ 25 Answers ]

I have a 7 year old in 2nd grade. They are learning their multiplication tables, but my daughter is just not retaining anything we go over. We have tried just having her memorize and we have tried explaining that 2 x 3 is 2 added up 3 times. I have tried flash cards, quizzing her, etc. Any...

Error in tables [ 1 Answers ]

table1 product pk is productID table2 problems pk is problemID table3 severity pk is severityId table4 users pk is userID when I used in then message is incorrect syntes near inner Const sSelect As String = "select problems.problemID," & "problems.reportedon," &...

View more questions Search