Thread: 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:
SELECTcall_id,phone_number,(SELECT countryFROM prefixesWHERE 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
Hi Mario,<br />over - partition by will help. Iy that syntax is uncomfortable you can use multiple joins, using maximum
lengthor max(prefix::int) but that will slow down the process. select over - partition by is fastest solution i
think.<br/><br /><br /><br /><div class="gmail_quote">On Sat, Dec 10, 2011 at 11:24 PM, Mario Splivalo <span
dir="ltr"><<ahref="mailto:mario.splivalo@megafon.hr">mario.splivalo@megafon.hr</a>></span> wrote:<br
/><blockquoteclass="gmail_quote" style="border-left:1px solid rgb(204,204,204);margin:0pt 0pt 0pt
0.8ex;padding-left:1ex">I have a table called 'calls' which holds 'call detail records'. Let's<br /> assume the table
lookslike this:<br /><br /> CREATE TABLE cdr (<br /> call_id serial,<br /> phone_number text<br /> );<br
/><br/> And I have a table with country call prefixes, that looks like this:<br /><br /> CREATE TABLE prefixes (<br />
prefix text,<br /> country text<br /> );<br /><br /> And now some test data:<br /><br /> INSERT INTO
prefixesVALUES ('1', 'USA');<br /> INSERT INTO prefixes VALUES ('44', 'UK');<br /> INSERT INTO prefixes VALUES ('385',
'Croatia');<br/> INSERT INTO prefixes VALUES ('387', 'Bosnia');<br /> INSERT INTO prefixes VALUES ('64', 'New
Zeland');<br/> INSERT INTO prefixes VALUES ('642', 'New Zeland Mobile');<br /> INSERT INTO calls VALUES (1,
'11952134451');<br/> INSERT INTO calls VALUES (2, '448789921342');<br /> INSERT INTO calls VALUES (3,
'385914242232');<br/> INSERT INTO calls VALUES (4, '385914242232');<br /> INSERT INTO calls VALUES (5,
'645122231241');<br/> INSERT INTO calls VALUES (6, '444122523421');<br /> INSERT INTO calls VALUES (7,
'64212125452');<br/> INSERT INTO calls VALUES (8, '1837371211');<br /> INSERT INTO calls VALUES (9, '11952134451');<br
/>INSERT INTO calls VALUES (10, '448789921342');<br /> INSERT INTO calls VALUES (11, '385914242232');<br /> INSERT INTO
callsVALUES (12, '385914242232');<br /> INSERT INTO calls VALUES (13, '645122231241');<br /> INSERT INTO calls VALUES
(14,'4441232523421');<br /> INSERT INTO calls VALUES (15, '64112125452');<br /> INSERT INTO calls VALUES (16,
'1837371211');<br/><br /><br /> Now, if I want to have a 'join' between those two tables, here is what I<br /> am doing
rightnow:<br /><br /> SELECT<br /> call_id,<br /> phone_number,<br /> (SELECT<br />
country<br/> FROM<br /> prefixes<br /> WHERE<br /> calls.phone_number LIKE
prefix|| '%'<br /> ORDER BY<br /> length(prefix) DESC LIMIT 1<br /> ) AS country<br />
FROMcalls;<br /><br /><br /> Is there a way I could use join here? I can do something like:<br /><br /> SELECT ... FROM
callsJOIN prefixes ON calls.phone_number LIKE prefix || '%'<br /><br /> but I'd get duplicate rows there (for instance,
forNew Zeland calls,<br /> from my test data).<br /><br /> Or should I add 'prefix' field to the calls table, and then
doa inner<br /> join with prefixes table?<br /><br /> Mario<br /><font color="#888888"><br /> --<br /> Sent via
pgsql-sqlmailing list (<a href="mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</a>)<br /> To make changes to
yoursubscription:<br /><a href="http://www.postgresql.org/mailpref/pgsql-sql"
target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br/></font></blockquote></div><br /><br clear="all"
/><br/>-- <br />---------------------------------------<br />Viktor Bojović<br
/>---------------------------------------<br/>Wherever I go, Murphy goes with me<br />
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
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:
SELECTcall_id,phone_number,(SELECT countryFROM prefixesWHERE 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