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

Hi Mario,
over - partition by will help. Iy that syntax is uncomfortable you can use
multiple joins, using maximum length or max(prefix::int) but that will slow
down the process. select over - partition by is fastest solution i think.

On Sat, Dec 10, 2011 at 11:24 PM, Mario Splivalo
<mario(dot)splivalo(at)megafon(dot)hr>wrote:

> 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>

--
---------------------------------------
Viktor Bojović
---------------------------------------
Wherever I go, Murphy goes with me