In article <[EMAIL PROTECTED]>,
"Jason Glicken" <[EMAIL PROTECTED]> writes:
> I have 2 tables set up in MySQL, one with a dialed number field and
> duration, the other with a list of country codes, there names, and the
> rates. I am trying to match the dialed number with country code. My
> problem is I cannot get the results based on the longest possible match?
> I am not even sure if the query is correct, but I feel like I am close:
mysql> select distinctrow a.calldate, a.src, substring(a.dst,4,5),
> sec_to_time(a.billsec) as billsec, format((a.billsec/60 * b.rate), 2)
> totalcost, b.destination, b.name from cdr a left join rates b on
> substring(a.dst,4,5) regexp (concat('^[2-9]?', b.destination)) where
> src='erick' and dst like '011%' group by calldate order by 'calldate'
> desc;
> For example (5114445555 = a.dst) and it matches with (51 =
> b.destination), (Peru = b.name).
> I need this to match (511 = b.destination), (Peru Lima = b.name)
Try something like the following:
SELECT a.dst, b.destination, b.name
FROM cdr a
JOIN rates b ON a.dst LIKE concat (b.destination, '%')
LEFT JOIN rates c ON a.dst LIKE concat (c.destination, '%')
AND length (c.destination) > length (b.destination)
WHERE c.id IS NULL
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
