sql - Calculate call cost from rate table using MySQL -

i trying compare call rates between 2 telephone providers. have 2 tables, follows:

create table 18185_rates (   calldate date,   calltime time,   calledno varchar(20),   duration integer(8),   callcost float(5 , 3 ) );  create table int_rates (   dialcode varchar(20),   description varchar(20),   callcost float(5 , 3 ) ); 

the 18185_rates contains call data records phone system, example values follows:

calldate,calltime,calledno,duration,callcost 2013-07-30,11:21:38,35342245738,10,0.050 2013-07-30,16:19:25,353872565822,37,0.130 2013-08-02,08:31:12,65975636187,1344,0.270 2013-08-05,11:03:53,919311195965,2356,1.640 

the table int_rates contains tariff data calls provider in following format:

dialcode,description,callcost 1,usa,0.012 1204,canada,0.008 1204131,canada,0.018 1226,canada,0.008 1226131,canada,0.018 1242,bahamas,0.137 1242357,bahamas mobile,0.251 1242359,bahamas mobile,0.251 

i trying run comparison, can see how calls in 18185_rates have cost other provider. can't work out how join 2 tables based on variable length dialling code in int_rates.

after @gordon linoff below, i've come following code:

select      r.*,     (select permin      int_rates ir1      r.calledno concat(ir1.dialcode, '%')      order dialcode desc      limit 1) newcostpermin   18185_rates r; 

i assuming want match each phone number cost has longest prefix. here approach:

select ir.*,        (select callcost         int_rates ir         r.calledno concat(ir.dialcode, '%')         order length(ir.dialcode) desc         limit 1        ) theircost 18185_rates r; 

this using correlated subquery find longest dialcode matches beginning of call. null if nothing matches. also, not efficient , cannot use indexes.

edit:

there different ways approach this. simplest duplicate subquery:

select ir.*,        (select callcost         int_rates ir         r.calledno concat(ir.dialcode, '%')         order length(ir.dialcode) desc         limit 1        ) theircost,        (select description         int_rates ir         r.calledno concat(ir.dialcode, '%')         order length(ir.dialcode) desc         limit 1        ) theirdescription 18185_rates r; 

in practice, pull primary key out in first subquery , join table, whatever fields want table. however, don't specify table layout , if first query has reasonable performance, doing twice should ok too.