Thread: Weird join...

  I need to join two tables in postgres (or any other SQL) like this:
table 1 has a list of phone numbers
table 2 has a list of country prefixes (like 40 for romania 407 for romania
mobile, etc)

I want to join the tables so that a number like
407111111 in table 1
is joined ONLY with
407 in table 2

Can you please help?

Thank you

On Mon, Jul 12, 2004 at 12:47:41 +0300,
  Costin Manda <costin@interpoint.ro> wrote:
>   I need to join two tables in postgres (or any other SQL) like this:
> table 1 has a list of phone numbers
> table 2 has a list of country prefixes (like 40 for romania 407 for romania
> mobile, etc)
>
> I want to join the tables so that a number like
> 407111111 in table 1
> is joined ONLY with
> 407 in table 2
>
> Can you please help?

You should keep the country prefixes as a separate column in the table with
the full numbers. It might also be useful to break out area/city code
from the local phone number.
You also might want to store both the IDD prefixes (there can be multiple
ones for some countries) and the NDD prefixes for the phone numbers.

On Tue, Jul 13, 2004 at 12:02:38 +0300,
  Costin Manda <costin@interpoint.ro> wrote:

Please keep responses posted to the list while discussing the problem
so that others can comment and learn from the discussion.

>   Well, I don't have that option. I have the tables already and they are
> continually changed by programs not under my control. I need to join them
> like I said above.
>
>   If there is no SQL method to do it, then I guess I must do it with a
> temporary table and another join, but I am wondering if it is a simpler way,
> because another table and join would just slow things down.

If you know how to extract the country code from the full phone number
then you can use that expression/function in a join and it will do what
you want. You will probably want to make an index on that expression
or function.

Assuming:
  table Phone(Number varchar(11))
  table Country(Prefix varchar(11), Name varchar(99), ...)

select    *
from       Phone, Country
where    Prefix = (select max(Prefix)  from Country where Number like
Prefix+'%')

""Costin Manda"" <costin@interpoint.ro> wrote in message
news:00a401c467f5$464b3980$96b0e6c1@Costin...
>   I need to join two tables in postgres (or any other SQL) like this:
> table 1 has a list of phone numbers
> table 2 has a list of country prefixes (like 40 for romania 407 for
romania
> mobile, etc)
>
> I want to join the tables so that a number like
> 407111111 in table 1
> is joined ONLY with
> 407 in table 2