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


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

On Mon, Jul 12, 2004 at 12:47:41 +0300,
Costin Manda <costin (AT) interpoint (DOT) ro> wrote:
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.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

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 (AT) interpoint (DOT) ro> wrote

On Tue, Jul 13, 2004 at 12:02:38 +0300,
Costin Manda <costin (AT) interpoint (DOT) ro> wrote:

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

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.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster