Hi,

I have a table containing calls to different destinations, and a table
with cost and prefixes.
I would like to select just the best match prefix for any given call
(e.g. for the call with dst 123, the prefix 12 and the prefix 1 would
both match, but 123 would be the best match)

These are the tables which I'm testing with

calls
+---------------------+--------+-----+---------+
| calldate | callid | dst | billsec |
+---------------------+--------+-----+---------+
| 0000-00-00 00:00:00 | 1 | 123 | 40 |
| 0000-00-00 00:00:00 | 2 | 123 | 240 |
| 0000-00-00 00:00:00 | 3 | 123 | 245 |
| 0000-00-00 00:00:00 | 4 | 123 | 0 |
+---------------------+--------+-----+---------+

tariff
+-------------+--------+------+
| destination | prefix | cost |
+-------------+--------+------+
| Cost 1 | 1 | 50 |
| Cost 2 | 12 | 50 |
| Cost | 234 | 100 |
+-------------+--------+------+



Any suggestion how I can do this?

Thakns,
Oliver

Oliver <oli77za@yahoo.co.uk> wrote in
<1193706246.690698.301340@d55g2000hsg.googlegroups .com>:
> I have a table containing calls to different destinations,
> and a table with cost and prefixes.
> I would like to select just the best match prefix for any
> given call (e.g. for the call with dst 123, the prefix 12
> and the prefix 1 would both match, but 123 would be the
> best match)
>
> These are the tables which I'm testing with

[snip]

There's probably a better solution, but a quick hack would
be:

SELECT *
FROM
calls AS c LEFT JOIN
(SELECT
pc.callid,MIN(LENGTH(pt.prefix)) AS l
FROM
calls AS pc LEFT JOIN
tariff AS pt ON LOCATE(pt.prefix,pc.dst)=1
GROUP BY pc.callid
) AS pl ON c.callid=pl.callid LEFT JOIN
tariff AS t ON
LOCATE(t.prefix,c.dst)=1 AND LENGTH(t.prefix)=pl.l;

--
It is rare to find learned men who are clean, do not stink,
and have a sense of humour. -- Liselotte in a letter to
Sophie, 30 Jul 1705