[psychlopes]

Hello...

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:
98732547865

Matching prefixes in table 2 may be as follows:
98
987
9873

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
Sam

[jstrike]

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]

select y.*,z.*
from
(select a.*
from tbl1 a) y
left join
(select b.prefix
from table2 b) z on SUBSTRING(y.phone,1,LEN(z.PREFIX)) = z.PREFIX

[crigby]

Hi,
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 :: 12.2.8.1 JOIN Syntax
Peace,
Clarke