Query Problem... Left OuterJoin / Tagging Issue - Mailing list pgsql-sql

<small><font face="Courier New, Courier, monospace">Hi,<br /><br /> I've been working on this for quite awhile now and
don'tseem to get the proper query.<br /><br /> I have basically 4 tables. <br /><br /> 1. Table john_test contains the
numbersof the calls.<br /> 2. Table john_country contains the country with prefix.<br /> 3. Table john_clients contains
theclients and their sub_id's <br />     (because a client usually has a subclient, [ex. highway-2, highway-3]) and
theirprefix.<br /> 4. Table john_test contains the phone numbers.<br /></font></small><small><font face="Courier New,
Courier,monospace"><br /> ----------------------------------------------------------------</font></small><small><font
face="CourierNew, Courier,
monospace">----------------------------------------------------------------</font></small><br/><small><font
face="CourierNew, Courier, monospace"><br /> select * from john_client_rate limit 3;  <br /><br />    name   |   
country    | cali | cana | callrate | dir_id | trans_id | svc_id | base | incr | client_id <br />
----------+----------------+------+------+----------+--------+----------+--------+------+------+-----------<br/>
 highway | Afghanistan    |      |      |   0.6212 |      0 | 0        |        |    6 |    6 |         4<br />
 highway | Albania        |      |      |   0.3945 |      0 | 1        |        |    6 |    6 |         4<br />
 highway | Bahamas        |      |      |   0.0513 |      0 | 1        |        |    6 |    6 |         4<br /> (3
rows)<br/><br /></font></small><small><font face="Courier New, Courier, monospace">select * from john_country limit
3;</font></small><br/><small><font face="Courier New, Courier, monospace"><br />     country    | state |  prefix  |
area_code<br /> ---------------+-------+----------+-----------<br />  Afghanistan   |       | 93       | <br />  Aland
Islands|       | 35818120 | <br />  Albania       |       | 355      | <br /> (3 rows)<br /><br /><br
/></font></small><small><fontface="Courier New, Courier, monospace">select * from john_clients limit 3;<br /><br
/></font></small><small><fontface="Courier New, Courier, monospace"> id | client_id | sub_id | name     | prefix  | 
type <br /> ----+-----------+--------+----------+---------+--------<br />  80 |        80 |      0 | highway  | 71081  
|client<br />  80 |        80 |      0 | </font></small><small><font face="Courier New, Courier,
monospace">highway</font></small><small><fontface="Courier New, Courier, monospace">  | 7107011 | client<br />  80
|       80 |      0 | </font></small><small><font face="Courier New, Courier,
monospace">highway</font></small><small><fontface="Courier New, Courier, monospace">  | 71091   | client<br /> (3
rows)<br/><br /></font></small><small><font face="Courier New, Courier, monospace"> select * from john_test limit
3;</font></small><br/><small><font face="Courier New, Courier, monospace"><br /></font></small><small><font
face="CourierNew, Courier, monospace"> client_id |  name   |       phonenum       |     calledphonenum      |
phonenumtranslat| direction | duration <br />
----------+---------+----------------------+-------------------------+------------------+-----------+----------<br/>
       2 | highway | 83863011351927330133 | 20100147011351927330133 |                  | outbound  |      363<br />
       2 | highway | 83863011441179218126 | 1943011441179218126     |                  | outbound  |       83<br />
       2 | highway | 83863011441179218126 | 20100147011441179218126 |                  | outbound  |       32<br /> (3
rows)<br/><br /></font></small><small><font face="Courier New, Courier,
monospace">----------------------------------------------------------------</font></small><small><fontface="Courier
New,Courier, monospace">----------------------------------------------------------------</font></small><br
/><small><fontface="Courier New, Courier, monospace"><br /> What I want to do is to remove the prefix, and retain the
numberusing the following query:<br /><br /></font></small><small><font face="Courier New, Courier, monospace">select 
<br/> john_test.name, <br /> john_test.gwrxdcdn, <br /> john_test.duration as dur, <br /> john_client_rate.name as
name2,<br /> john_client_rate.country, <br /> john_country.prefix, <br /> substring(john_test.gwrxdcdn from
length(john_country.prefix)+1)as strip, <br />
get_duration(john_test.duration::int,john_client_rate.base,john_client_rate.incr)as realdur <br /> from john_test <br
/>left outer join john_client_rate <br /> on (prefix in <br />    ( <br />       select prefix from john_client_rate
<br/>       where john_test.phonenum ~ ( '^' || john_country.prefix) <br />       order by length(john_country.prefix)
desclimit '1'   <br />    ) <br />    ) <br /> limit 20;<br /><br /></font></small><small><font face="Courier New,
Courier,monospace">----------------------------------------------------------------</font></small><small><font
face="CourierNew, Courier, monospace">----------------------------------------------------------------<br /><br /> I
haveachieved this already, now I want to identify which country it's supposed to be for.<br /> Problem is sometimes the
"stripped"number that is retained shows: 8661234567 or 8889876543<br /> This would indicate that the call is already
tollfree without me being able to identify the country. <br /> How can I get over this? <br /><br /> Further, I am
planningto use multiple joins since I have several tables and so as to identify missing countries. On this questions
whichquery is better?<br /><br /> Query 1:<br /><br /> Select table1.column,table2.column,table3.column from table1
leftouter join table 2 on (table1.column=table2.column) left outer join table3 on (table2.column=table3.column) ;<br
/><br/> or Query 2:<br /><br /></font></small><small><font face="Courier New, Courier, monospace">Select
table1.column,table2.column,table3.columnfrom table1,table2,table3 where [conditions] ;<br /><br /> Ultimately, I want
torun one query that will satisfy these things and help me insert into a table that will have it "TAGGED" properly with
theright Country, Client(name), prefix, and Rate for computation with Duration.<br /><br /> Hope you can reply to me
asap.This is of urgent importance. Thank you and any help would be greatly appreciated!<br /><br /> - JT<br
/></font></small>