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:
This was regexp test - did not match from beginning of string ( all
results wrong)
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;
correct query
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('^', 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)
Any suggestions would be greatly appreciated.
Thanks in advance,
Jason Glicken
