title: Calculate call cost from rate table using MySQL
body: <p>I am trying to compare call rates between two telephone providers. I have two tables, as follows:</p>
  
  <pre><code>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 )
  );
  </code></pre>
  
  <p>The 18185_rates contains call data records from a phone system, some example values as follows:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>The table int_rates contains tariff data for calls from another provider in the following format:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I am trying to run a comparison, so that I can see how much the calls in 18185_rates would have cost with the other provider. I can't work out how to join the two tables based on the variable length dialling code in int_rates.</p>
  
  <p>After some help from @Gordon Linoff below, I've come up with the following code:</p>
  
  <pre><code>SELECT 
      r.*,
      (SELECT permin
       FROM int_rates ir1
       WHERE r.calledno LIKE CONCAT(ir1.dialcode, '%')
       ORDER BY dialcode DESC
       LIMIT 1) AS newcostpermin
  FROM
    18185_rates r;
  </code></pre>