Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id CA15D5EC6B8 for ; Mon, 16 Jan 2012 04:43:58 -0400 (AST) Received: from cluster1.pc3ph.com ([202.124.134.2]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1RmiAT-0007HD-3N for pgsql-sql@postgresql.org; Mon, 16 Jan 2012 08:43:58 +0000 Received: from [172.16.10.215] (intra.eglobalreach.net [202.124.138.210]) by cluster1.pc3ph.com (8.14.3/) with ESMTP id q0G8hZOC014153; Mon, 16 Jan 2012 16:43:35 +0800 Message-ID: <4F13E337.6040305@htechcorp.net> Date: Mon, 16 Jan 2012 16:43:35 +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: David Johnston CC: John Tuliao , "pgsql-sql@postgresql.org" Subject: Re: Query Problem... Left OuterJoin / Tagging Issue References: <4F0FB38A.2020809@eglobalreach.net> <85B4919C-C7AE-45BE-B32B-52B5B4F409FD@yahoo.com> In-Reply-To: <85B4919C-C7AE-45BE-B32B-52B5B4F409FD@yahoo.com> Content-Type: multipart/alternative; boundary="------------030209000502050802060007" X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201201/70 X-Sequence-Number: 36241 This is a multi-part message in MIME format. --------------030209000502050802060007 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit Thank you so much for your prompt reply David. I will consider your advice and put it to mind and action. I hope you all don't get tired of helping! For now, I will note down what I need to and do the necessary adjustments. Thank you for your time! On Friday, 13 January, 2012 10:26 PM, David Johnston wrote: > On Jan 12, 2012, at 23:31, John Tuliao > wrote: > >> 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; > > If you have a select within the ON clause of a join it isn't really a > join. ON clauses should be simple expressions (almost always > equality) between fields on the two tables with AND/OR logic. > >> >> -------------------------------------------------------------------------------------------------------------------------------- >> >> 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] ; >> > > Query 1 is an outer join, query 2 is an inner join; totally different > semantics so the question is more "which one will work" versus "which > one is better". Do you at least understand the difference? > >> 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 > > > Ignore this specific query for the moment and just figure out the > various relationships between the tables. Once you have that the > queries become much easier. > > Write out your desired output columns, with table prefixes, and mark > whether each on is optional or mandatory. Tables with optional fields > are outer joined to other tables, ideally those with only mandatory > fields. The corresponding ON clauses should use simple equalities, > though you may modify the the comparison values using functions. > > It sounds like you need to take a step back and do some serious > reading on SQL basics, though I'll give you credit for at least trying > and being somewhat descriptive of your goal. > > David J. --------------030209000502050802060007 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable X-MIME-Autoconverted: from 8bit to quoted-printable by cluster1.pc3ph.com id q0G8hZOC014153 Thank you so much for your prompt reply David. I will consider your advice and put it to mind and action. I hope you all don't get tired of helping!

For now, I will note down what I need to and do the necessary adjustments. Thank you for your time!

On Friday, 13 January, 2012 10:26 PM, David Johnston wrote:
On Jan 12, 2012, at 23:31, John Tuliao <jptuliao@eglobalreach.net> wrote:

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
=C2=A0=C2=A0=C2=A0 (because a client usually has a subclien= t, [ex. highway-2, highway-3]) and their prefix.
4. Table john_test contains the phone numbers.

----------------------------------------------------------------------------------= ----------------------------------------------

select * from john_client_rate limit 3;=C2=A0

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

select * from john_country limit 3;

=C2=A0=C2=A0=C2=A0 country=C2=A0=C2=A0=C2=A0 | state |=C2=A0= prefix=C2=A0 | area_code
---------------+-------+----------+-----------
=C2=A0Afghanistan=C2=A0=C2=A0 |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0 | 93=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 |
=C2=A0Aland Islands |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 |= 35818120 |
=C2=A0Albania=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 |=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0 | 355=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 |
(3 rows)


select * from john_clients limit 3;

=C2=A0id | client_id | sub_id | name =C2=A0 =C2=A0= | prefix=C2=A0 |=C2=A0 type=C2=A0
----+-----------+--------+----------+---------+--------
=C2=A080 |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 80 |=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0 0 | highway=C2=A0 | 71081=C2=A0=C2=A0 | clien= t
=C2=A080 |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 80 |=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0 0 | highway=C2=A0 | 7107011 | client
=C2=A080 |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 80 |=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0 0 | highway=C2=A0 | 71091=C2=A0= =C2=A0 | client
(3 rows)

 select * from john_test limit 3;=

client_id |=C2=A0 name=C2=A0=C2=A0 |=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0 phonenum =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 |=C2=A0=C2=A0=C2=A0=C2=A0 calledphonenum=C2=A0 =C2=A0=C2=A0= =C2=A0 | phonenumtranslat | direction | duration
----------+---------+----------------------+-------------------------+---= ---------------+-----------+----------
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 2 | highway | 83= 863011351927330133 | 20100147011351927330133 |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | outb= ound=C2=A0 |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 363
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 2 | highway | 83= 863011441179218126 | 1943011441179218126=C2=A0=C2=A0=C2=A0=C2=A0 |=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0 | outbound=C2=A0 |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 83
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 2 | highway | 83= 863011441179218126 | 20100147011441179218126 |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 | outb= ound=C2=A0 |=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 32
(3 rows)

------------------------------------------------= ----------------------------------= ----------------------------------------------

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

select=C2=A0
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
=C2=A0=C2=A0 (
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 select prefix from john_clie= nt_rate
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 where john_test.phonenum ~ (= '^' || john_country.prefix)
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 order by length(john_country= .prefix) desc limit '1'=C2=A0=C2=A0
=C2=A0=C2=A0 )
=C2=A0=C2=A0 )
limit 20;

If you have a select within the ON clause of a join it isn't really a join. =C2=A0ON clauses should be simple expressions (alm= ost always equality) between fields on the two tables with AND/OR logic.


------------------------------------------------= ----------------------------------= ----------------------------------------------

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=3Dtable2.column) left outer join table3 on (table2.column=3Dtable3.column) ;

or Query 2:

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


Query 1 is an outer join, query 2 is an inner join; totally different semantics so the question is more "which one will work" versus "which one is better". =C2=A0Do you at least underst= and the difference?

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


Ignore this specific query for the moment and just figure out the various relationships between the tables. =C2=A0Once you have that = the queries become much easier.

Write out your desired output columns, with table prefixes, and mark whether each on is optional or mandatory. =C2=A0Tables with optional fields are outer joined to other tables, ideally those with only mandatory fields. =C2=A0The corresponding ON clauses should use simple equalities, though you may modify the the comparison values using functions.=C2=A0

It sounds like you need to take a step back and do some serious reading on SQL basics, though I'll give you credit for at least trying and being somewhat descriptive of your goal.

David J.

--------------030209000502050802060007--