Re: Query Problem... Left OuterJoin / Tagging Issue

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(at)eglobalreach(dot)net
> <mailto:jptuliao(at)eglobalreach(dot)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
>> (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.