Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
PaTRiCKDRD
Yak Posting Veteran
55 Posts |
 Posted - 2005-08-03 : 04:39:13
|
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 T2ON 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. |
|
|
|
|
|
SQL query to join prefix with full number