I have a table called 'calls' which holds 'call detail records'. Let's assume the table looks like this:
CREATE TABLE cdr ( call_id serial, phone_number text ); And I have a table with country call prefixes, that looks like this: CREATE TABLE prefixes ( prefix text, country text ); And now some test data: INSERT INTO prefixes VALUES ('1', 'USA'); INSERT INTO prefixes VALUES ('44', 'UK'); INSERT INTO prefixes VALUES ('385', 'Croatia'); INSERT INTO prefixes VALUES ('387', 'Bosnia'); INSERT INTO prefixes VALUES ('64', 'New Zeland'); INSERT INTO prefixes VALUES ('642', 'New Zeland Mobile'); INSERT INTO calls VALUES (1, '11952134451'); INSERT INTO calls VALUES (2, '448789921342'); INSERT INTO calls VALUES (3, '385914242232'); INSERT INTO calls VALUES (4, '385914242232'); INSERT INTO calls VALUES (5, '645122231241'); INSERT INTO calls VALUES (6, '444122523421'); INSERT INTO calls VALUES (7, '64212125452'); INSERT INTO calls VALUES (8, '1837371211'); INSERT INTO calls VALUES (9, '11952134451'); INSERT INTO calls VALUES (10, '448789921342'); INSERT INTO calls VALUES (11, '385914242232'); INSERT INTO calls VALUES (12, '385914242232'); INSERT INTO calls VALUES (13, '645122231241'); INSERT INTO calls VALUES (14, '4441232523421'); INSERT INTO calls VALUES (15, '64112125452'); INSERT INTO calls VALUES (16, '1837371211'); Now, if I want to have a 'join' between those two tables, here is what I am doing right now: SELECT call_id, phone_number, (SELECT country FROM prefixes WHERE calls.phone_number LIKE prefix || '%' ORDER BY length(prefix) DESC LIMIT 1 ) AS country FROM calls; Is there a way I could use join here? I can do something like: SELECT ... FROM calls JOIN prefixes ON calls.phone_number LIKE prefix || '%' but I'd get duplicate rows there (for instance, for New Zeland calls, from my test data). Or should I add 'prefix' field to the calls table, and then do a inner join with prefixes table? Mario -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql