Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 7A7181F4FC59 for ; Fri, 13 Jan 2012 00:31:37 -0400 (AST) Received: from vanguard.eglobalreach.net ([202.124.128.4]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1RlYnZ-0000JZ-5C for pgsql-sql@postgresql.org; Fri, 13 Jan 2012 04:31:34 +0000 Received: from [172.16.10.215] (intra.eglobalreach.net [202.124.138.210]) by vanguard.eglobalreach.net (8.13.8/8.13.8) with ESMTP id q0D4V6pE016616 for ; Fri, 13 Jan 2012 12:31:06 +0800 Message-ID: <4F0FB38A.2020809@eglobalreach.net> Date: Fri, 13 Jan 2012 12:31:06 +0800 From: John Tuliao User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.2.24) Gecko/20111108 Thunderbird/3.1.16 MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Query Problem... Left OuterJoin / Tagging Issue Content-Type: multipart/alternative; boundary="------------020407000907040709070501" X-Pg-Spam-Score: -0.3 (/) X-Archive-Number: 201201/53 X-Sequence-Number: 36224 This is a multi-part message in MIME format. --------------020407000907040709070501 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Hi, I've been working on this for quite awhile now and don't seem to get the proper query. I have basically 4 tables. 1. Table john_test contains the numbers of the calls. 2. Table john_country contains the country with prefix. 3. Table john_clients contains the clients and their sub_id's (because a client usually has a subclient, [ex. highway-2, highway-3]) and their prefix. 4. Table john_test contains the phone numbers. -------------------------------------------------------------------------------------------------------------------------------- select * from john_client_rate limit 3; name | country | cali | cana | callrate | dir_id | trans_id | svc_id | base | incr | client_id ----------+----------------+------+------+----------+--------+----------+--------+------+------+----------- highway | Afghanistan | | | 0.6212 | 0 | 0 | | 6 | 6 | 4 highway | Albania | | | 0.3945 | 0 | 1 | | 6 | 6 | 4 highway | Bahamas | | | 0.0513 | 0 | 1 | | 6 | 6 | 4 (3 rows) select * from john_country limit 3; country | state | prefix | area_code ---------------+-------+----------+----------- Afghanistan | | 93 | Aland Islands | | 35818120 | Albania | | 355 | (3 rows) select * from john_clients limit 3; id | client_id | sub_id | name | prefix | type ----+-----------+--------+----------+---------+-------- 80 | 80 | 0 | highway | 71081 | client 80 | 80 | 0 | highway | 7107011 | client 80 | 80 | 0 | highway | 71091 | client (3 rows) select * from john_test limit 3; client_id | name | phonenum | calledphonenum | phonenumtranslat | direction | duration ----------+---------+----------------------+-------------------------+------------------+-----------+---------- 2 | highway | 83863011351927330133 | 20100147011351927330133 | | outbound | 363 2 | highway | 83863011441179218126 | 1943011441179218126 | | outbound | 83 2 | highway | 83863011441179218126 | 20100147011441179218126 | | outbound | 32 (3 rows) -------------------------------------------------------------------------------------------------------------------------------- What I want to do is to remove the prefix, and retain the number using the following query: select john_test.name, john_test.gwrxdcdn, john_test.duration as dur, john_client_rate.name as name2, john_client_rate.country, john_country.prefix, substring(john_test.gwrxdcdn from length(john_country.prefix)+1) as strip, get_duration(john_test.duration::int,john_client_rate.base,john_client_rate.incr) as realdur from john_test left outer join john_client_rate on (prefix in ( select prefix from john_client_rate where john_test.phonenum ~ ( '^' || john_country.prefix) order by length(john_country.prefix) desc limit '1' ) ) limit 20; -------------------------------------------------------------------------------------------------------------------------------- I have achieved this already, now I want to identify which country it's supposed to be for. Problem is sometimes the "stripped" number that is retained shows: 8661234567 or 8889876543 This would indicate that the call is already toll free without me being able to identify the country. How can I get over this? Further, I am planning to use multiple joins since I have several tables and so as to identify missing countries. On this questions which query is better? Query 1: Select table1.column,table2.column,table3.column from table1 left outer join table 2 on (table1.column=table2.column) left outer join table3 on (table2.column=table3.column) ; or Query 2: Select table1.column,table2.column,table3.column from table1,table2,table3 where [conditions] ; Ultimately, I want to run one query that will satisfy these things and help me insert into a table that will have it "TAGGED" properly with the right Country, Client(name), prefix, and Rate for computation with Duration. Hope you can reply to me asap. This is of urgent importance. Thank you and any help would be greatly appreciated! - JT --------------020407000907040709070501 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Hi,

I've been working on this for quite awhile now and don't seem to get the proper query.

I have basically 4 tables.

1. Table john_test contains the numbers of the calls.
2. Table john_country contains the country with prefix.
3. Table john_clients contains the clients and their sub_id's
    (because a client usually has a subclient, [ex. highway-2, highway-3]) and their prefix.
4. Table john_test contains the phone numbers.

--------------------------------------------------------------------------------------------------------------------------------

select * from john_client_rate limit 3; 

   name   |    country     | cali | cana | callrate | dir_id | trans_id | svc_id | base | incr | client_id
----------+----------------+------+------+----------+--------+----------+--------+------+------+-----------
 highway  | Afghanistan    |      |      |   0.6212 |      0 | 0        |        |    6 |    6 |         4
 highway  | Albania        |      |      |   0.3945 |      0 | 1        |        |    6 |    6 |         4
 highway  | Bahamas        |      |      |   0.0513 |      0 | 1        |        |    6 |    6 |         4
(3 rows)

select * from john_country limit 3;

    country    | state |  prefix  | area_code
---------------+-------+----------+-----------
 Afghanistan   |       | 93       |
 Aland Islands |       | 35818120 |
 Albania       |       | 355      |
(3 rows)


select * from john_clients limit 3;

 id | client_id | sub_id | name     | prefix  |  type 
----+-----------+--------+----------+---------+--------
 80 |        80 |      0 | highway  | 71081   | client
 80 |        80 |      0 | highway  | 7107011 | client
 80 |        80 |      0 | highway  | 71091   | client
(3 rows)

 select * from john_test limit 3;

 client_id |  name   |       phonenum       |     calledphonenum      | phonenumtranslat | direction | duration
----------+---------+----------------------+-------------------------+------------------+-----------+----------
        2 | highway | 83863011351927330133 | 20100147011351927330133 |                  | outbound  |      363
        2 | highway | 83863011441179218126 | 1943011441179218126     |                  | outbound  |       83
        2 | highway | 83863011441179218126 | 20100147011441179218126 |                  | outbound  |       32
(3 rows)

--------------------------------------------------------------------------------------------------------------------------------

What I want to do is to remove the prefix, and retain the number using the following query:

select 
john_test.name,
john_test.gwrxdcdn,
john_test.duration as dur,
john_client_rate.name as name2,
john_client_rate.country,
john_country.prefix,
substring(john_test.gwrxdcdn from length(john_country.prefix)+1) as strip,
get_duration(john_test.duration::int,john_client_rate.base,john_client_rate.incr) as realdur
from john_test
left outer join john_client_rate
on (prefix in
   (
      select prefix from john_client_rate
      where john_test.phonenum ~ ( '^' || john_country.prefix)
      order by length(john_country.prefix) desc limit '1'  
   )
   )
limit 20;

--------------------------------------------------------------------------------------------------------------------------------

I have achieved this already, now I want to identify which country it's supposed to be for.
Problem is sometimes the "stripped" number that is retained shows: 8661234567 or 8889876543
This would indicate that the call is already toll free without me being able to identify the country.
How can I get over this?

Further, I am planning to use multiple joins since I have several tables and so as to identify missing countries. On this questions which query is better?

Query 1:

Select table1.column,table2.column,table3.column from table1 left outer join table 2 on (table1.column=table2.column) left outer join table3 on (table2.column=table3.column) ;

or Query 2:

Select table1.column,table2.column,table3.column from table1,table2,table3 where [conditions] ;

Ultimately, I want to run one query that will satisfy these things and help me insert into a table that will have it "TAGGED" properly with the right Country, Client(name), prefix, and Rate for computation with Duration.

Hope you can reply to me asap. This is of urgent importance. Thank you and any help would be greatly appreciated!

- JT
 --------------020407000907040709070501--