Re: Subselects to Joins? Or: how to design phone calls database

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 />