query problem

Hi All,
I have a problems with my query on how to come up with my desired results.
Below are my two tables with sample data:
tableRegion
--
ColPrefix | ColRegion
--
0044 | UK - National
00441 | UK - Mobile
001213 | USA - California
001 | USA
tableNumbers
--
ColPhone
--
0044123456789
0044712345678
0012131234567
0018001234567
I want to come up with results displaying PhoneNumbers with their
coresponding Region based on their prefix like below. Any solutions?
0044123456789 | UK - National
0044712345678 | UK - Mobile
0012131234567 | USA - California
0018001234567 | USA
Looking for help on this one. Thanks in advance!
joelPlease post not only expected results, but fully defined DDL and sample
data. See http://www.aspfaq.com/etiquette.asp?id=5006 for more details.
Also, I believe either your expected results are wrong, or you sample data
is wrong, because it looks like 0044123456789 should match UK Mobile, not UK
National.
Lastly, I would highly recommend normalizing tableNumbers to have a column
for region prefix, since it is obvious that you want to use it as its own
atomic value.
You might want to consider a new naming convention for your tables and
columns as well - it is not considered "best practice" to name an item for
what it is physically, but for what it represents conceptually - i.e.
PhoneNumber instead of ColPhone, Region instead of ColRegion, etc. (Of
course, "Best Practices" for one person are "Worst Habits" for another!)
Enough of the nitpicking - I think I have a solution that works. It could
definitely be optimized, but it functions as is.
SELECT P1.ColPhone, P1.ColRegion
FROM
(SELECT PHN.ColPhone, REG.ColRegion, LEN(REG.ColPrefix) AS PrefixLength
FROM tableNumbers PHN INNER JOIN tableRegions REG
ON PHN.ColPhone LIKE REG.ColPrefix + '%') P1
INNER JOIN
(SELECT PHN.ColPhone, MAX(LEN(REG.ColPrefix)) AS PrefixLength
FROM tableNumbers PHN INNER JOIN tableRegions REG
ON PHN.ColPhone LIKE REG.ColPrefix + '%'
GROUP BY PHN.ColPhone) P2
ON P1.ColPhone = P2.ColPhone
AND P1.PrefixLength = P2.PrefixLength
This is based on the understanding that all longer prefixes identify more
specific areas than shorter prefixes. So if you had:
ColPrefix | ColRegion
001213 | USA - California
0012 | USA - West Coast
001 | USA
then 001213 would take precedence over 0012, and 0012 would take precedence
over 001.
IHTH
Jeremy Williams
"Joel Gacosta" <joel@.gacosta.net> wrote in message
news:eR6G6UFbFHA.3400@.tk2msftngp13.phx.gbl...
> Hi All,
> I have a problems with my query on how to come up with my desired results.
> Below are my two tables with sample data:
> tableRegion
> --
> ColPrefix | ColRegion
> --
> 0044 | UK - National
> 00441 | UK - Mobile
> 001213 | USA - California
> 001 | USA
> tableNumbers
> --
> ColPhone
> --
> 0044123456789
> 0044712345678
> 0012131234567
> 0018001234567
>
> I want to come up with results displaying PhoneNumbers with their
> coresponding Region based on their prefix like below. Any solutions?
> 0044123456789 | UK - National
> 0044712345678 | UK - Mobile
> 0012131234567 | USA - California
> 0018001234567 | USA
> Looking for help on this one. Thanks in advance!
> joel
>|||Thanks Jeremy! You're right I posted a wrong sample data.
Anyway I'll take your advise and try your solution. That is exactly what I
meant.
"Jeremy Williams" <jeremydwill@.netscape.net> wrote in message
news:%23XV4LSGbFHA.3864@.TK2MSFTNGP10.phx.gbl...
> Please post not only expected results, but fully defined DDL and sample
> data. See http://www.aspfaq.com/etiquette.asp?id=5006 for more details.
> Also, I believe either your expected results are wrong, or you sample data
> is wrong, because it looks like 0044123456789 should match UK Mobile, not
> UK
> National.
> Lastly, I would highly recommend normalizing tableNumbers to have a column
> for region prefix, since it is obvious that you want to use it as its own
> atomic value.
> You might want to consider a new naming convention for your tables and
> columns as well - it is not considered "best practice" to name an item for
> what it is physically, but for what it represents conceptually - i.e.
> PhoneNumber instead of ColPhone, Region instead of ColRegion, etc. (Of
> course, "Best Practices" for one person are "Worst Habits" for another!)
> Enough of the nitpicking - I think I have a solution that works. It could
> definitely be optimized, but it functions as is.
> SELECT P1.ColPhone, P1.ColRegion
> FROM
> (SELECT PHN.ColPhone, REG.ColRegion, LEN(REG.ColPrefix) AS PrefixLength
> FROM tableNumbers PHN INNER JOIN tableRegions REG
> ON PHN.ColPhone LIKE REG.ColPrefix + '%') P1
> INNER JOIN
> (SELECT PHN.ColPhone, MAX(LEN(REG.ColPrefix)) AS PrefixLength
> FROM tableNumbers PHN INNER JOIN tableRegions REG
> ON PHN.ColPhone LIKE REG.ColPrefix + '%'
> GROUP BY PHN.ColPhone) P2
> ON P1.ColPhone = P2.ColPhone
> AND P1.PrefixLength = P2.PrefixLength
> This is based on the understanding that all longer prefixes identify more
> specific areas than shorter prefixes. So if you had:
> ColPrefix | ColRegion
> 001213 | USA - California
> 0012 | USA - West Coast
> 001 | USA
> then 001213 would take precedence over 0012, and 0012 would take
> precedence
> over 001.
> IHTH
> Jeremy Williams
> "Joel Gacosta" <joel@.gacosta.net> wrote in message
> news:eR6G6UFbFHA.3400@.tk2msftngp13.phx.gbl...
>|||That data is pretty nasty; definately consider revising the schema to
something more workable. I went after the problem using a ranking mechanism
to select which number gets associated with which prefix. I assumed the nam
e
of the tables were [tblNumbers] and [tblRegion].
Hope this helps!
declare @.prefix_rank table (rank int, ColPrefix varchar(20), ColRegion
varchar(20))
declare @.phonelist table (ColPhone varchar(30), ColRegion varchar(20))
declare @.loop int, @.level int
select @.loop = max(len(ColPrefix)) from tblRegion
set @.level = 1
while @.loop > 0
begin
if exists(select * from tblRegion where len(ColPrefix) = @.loop)
begin
insert into @.prefix_rank (rank, ColPrefix, ColRegion)
select @.level, ColPrefix, ColRegion from tblRegion where len(ColPrefix)
= @.loop
set @.level = @.level + 1
end
set @.loop = @.loop - 1
end
set @.loop = 1
while @.loop <= @.level
begin
insert into @.phonelist (ColPhone, ColRegion)
select tn.ColPhone, pr.ColRegion
from tblNumbers tn
,@.prefix_rank pr
where tn.ColPhone like pr.ColPrefix + '%'
and tn.ColPhone not in (select ColPhone from @.phonelist)
and pr.rank = @.loop
and not exists (select pl.* from @.phonelist pl where pl.ColPhone =
tn.ColPhone)
set @.loop = @.loop + 1
end
select * from @.phonelist
"Joel Gacosta" wrote:

> Hi All,
> I have a problems with my query on how to come up with my desired results.
> Below are my two tables with sample data:
> tableRegion
> --
> ColPrefix | ColRegion
> --
> 0044 | UK - National
> 00441 | UK - Mobile
> 001213 | USA - California
> 001 | USA
> tableNumbers
> --
> ColPhone
> --
> 0044123456789
> 0044712345678
> 0012131234567
> 0018001234567
>
> I want to come up with results displaying PhoneNumbers with their
> coresponding Region based on their prefix like below. Any solutions?
> 0044123456789 | UK - National
> 0044712345678 | UK - Mobile
> 0012131234567 | USA - California
> 0018001234567 | USA
> Looking for help on this one. Thanks in advance!
> joel
>
>