SQL query to join prefix with full number

[patrickdrd]

Good morning to all!

I work in a Telecom company, SQL Server database.
I have two tables, the first one holding the country's prefix, e.g. 123 for normal phone and 1234 for mobile.

The other table holds the calls, i.e. called number 123456789.

I want to write a query to join the call with the prefixes table to get the destination country, charge, etc.

I need not only join the two tables, but get the match with the maximum length as well.
As you know mobile calls cost more, so I want to search for a match with 1234 first
(maximum length).

I wrote a test query, I do not know if there is a better way to accomplish this though.

My query is as follows (T1 is the calls table, and T2 is the prefix table):

SELECT   *
FROM     T1 LEFT OUTER JOIN T2
ON       T1.t1_a LIKE T2.t2_a + '%'
AND      LEN(T2.t2_a) = (SELECT MAX(LEN(T2.t2_a))
                         FROM   T2
                         WHERE  T1.t1_a LIKE T2.t2_a + '%')

It seems to be working, but I do not like the double (?) join.

Any suggestions?

Thanks in advance.

 

[NoCoolHandle]

What about calculated columns...
added to the table to store the lenght.

This would then be easier to join on.You wouldn't have to do a couple fo calculations at runtime then. Indexing would be easier and query performance should improve.

e.g.

create table a
(c1 varchar(3000) not null, c2 as len(c1))
insert into a (c1) values('this is a test')
select * from a

The select Returns

this is a test 14

Just a thought.

Rob
ps


Rob