I think its definitely better to split phone number in calls table on 2 or even 3 parts... (Country prefix, carrier/area prefix, number)
Though maybe better design would be 3th table with full number as pk: PhoneNumbers (number, country prefix, optionally carrier/area prefix, rest of number) Then you can join calls to phonenumbers on full number string then join countries on country prefix... Kind Regards, Misa Sent from my Windows Phone From: Mario Splivalo Sent: 10 December 2011 23:27 To: pgsql-sql@postgresql.org Subject: [SQL] Subselects to Joins? Or: how to design phone calls database 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql