Question on 'Left outer join'

[dazz]

Hi, I am trying to join the table cis_prod.cst_full_period_rentals using left outer join, please see below. However, I keep getting the error:
"[Error Code: 0, SQL State: 42V01] ERROR: relation "cp" does not exist"

Can anyone say what Im doing wrong?

Thanks


select cp.product_code, sp.product_description, cp.unique_serv_id, cp.serv_no,
cp.owning_acct_no, cp.personal_rental, cp.cust_prod_no,
cp.inst_date, cp.price_var_exist, cp.cust_prod_status,
nm.first_name, nm.surname, nm.company_name,
fpr.fpr_bill_year, fpr.fpr_bill_month, fpr.fpr_rental_charge

from cis_prod.cst_customer_product cp,
cis_prod.cst_name nm,
cis_prod.sys_product sp

left outer join (select * from cis_prod.cst_full_period_rentals fp
where fp.fpr_bill_year = '2012' and fp.fpr_bill_month = '02' ) fpr
on cp.owning_acct_no = fpr.fpr_owning_acct_no

where (cp.product_code between 8640 and 8649 or cp.product_code between 8660 and 8669 ) and
cp.product_code = sp.product_code and
cp.cust_prod_status = 'W' and
cp.owning_acct_no='217004000000' and
cp.owning_acct_no = nm.acct_no and nm.stop_time_stamp = '31-DEC-9999' and
cp.cust_prod_no = fpr.fpr_cust_prod_no and
cp.product_code = fpr.fpr_product_code
;

[id10t]

Hi dazz!

Code: Select all

...
FROM 
    cis_prod.cst_customer_product AS cp,
    cis_prod.cst_name             AS nm,
    cis_prod.sys_product          AS sp
LEFT OUTER JOIN (...
...

Tables "cis_prod.cst_name" and "cis_prod.sys_product" do nothing in join. `Where`-clause is not a part of join, it just filters rows.

PS google keywords "join multiple tables".

[dazz]

Hi,

Thank you for responding. I updated the code as below, but it is still giving me syntax errors. The problem I have is with the syntax. If possible, can you please send me the code below with the proper syntax...thanks..

select cp.product_code, sp.product_description, cp.unique_serv_id, cp.serv_no,
cp.owning_acct_no, cp.personal_rental, cp.cust_prod_no,
cp.inst_date, cp.price_var_exist, cp.cust_prod_status,
nm.first_name, nm.surname, nm.company_name,
fpr.fpr_bill_year, fpr.fpr_bill_month, fpr.fpr_rental_charge

from cis_prod.cst_customer_product as cp,
cis_prod.cst_name as nm,
cis_prod.sys_product as sp

left outer join (select * from cis_prod.cst_full_period_rentals fp
where fp.fpr_bill_year = '2012' and fp.fpr_bill_month = '02' ) fpr
on cp.owning_acct_no = fpr.fpr_owning_acct_no

where (cp.product_code between 8640 and 8649 or cp.product_code between 8660 and 8669 ) and
cp.product_code = sp.product_code and
cp.cust_prod_status = 'W' and
cp.owning_acct_no='217004000000' and
cp.owning_acct_no = nm.acct_no and nm.stop_time_stamp = '31-DEC-9999' and
cp.cust_prod_no = fpr.fpr_cust_prod_no and
cp.product_code = fpr.fpr_product_code
;

[id10t]

I updated the code as below, but it is still giving me syntax errors...

My fault, didnt explain myself. Not in keyword "AS" a problem. Did you 'google: join multiple tables' ???

OUTER JOIN works between 2! tables (because properties of nulls in joins see PPPS).

If you need outer join for 3 tables - {A ,B ,C}, you should do for example join( A, join (B,C)). And what you have?

Code: Select all

FROM 
    tbl_1, tbl_2, tbl_3
LEFT OUTER JOIN 
    tbl_4
ON   
    tbl_1.col1 = tbl_4.col1 --- what about tbl_2, tbl_3 ????  what to do with them??? how to join (what kind of join - left, right, full, cross)???

PS 'where tbl_1.some_col = tbl_2.some_col' - it's not a join, its filter!!! (i.e. after join it filters rows... but AFTER JOIN is done)

PPS
1. http://www.codinghorror.com/blog/2007/1 ... joins.html
2. http://www.dbforums.com/ansi-sql/100934 ... query.html
3. http://www.wellho.net/solutions/mysql-l ... ables.html

PPPS If you still can't understand why try it like this - so try to draw all variants of Venn Diagram for 3 tables.
[HINT:: "left outer join(A, left outer join (B,C))" not equals to "left outer join(left outer join(A,B) , C)" ]

[JimKnicely]

I saw an issue like this in a MySQL database. I'm thinking its a bad idea to mix ANSI join syntax with comma separated table joins...

I re-created the tables to run a test... If I mix the JOIN types (like dazz's example) I get the same error with this query:

Code: Select all

dbadmin=> select count(*)
dbadmin->   from cst_customer_product as cp,
dbadmin->        cst_name as nm,
dbadmin->        sys_product as sp
dbadmin->   left outer join (select * from cst_full_period_rentals fp
dbadmin(>                     where fp.fpr_bill_year = '2012' and fp.fpr_bill_month = '02' ) fpr
dbadmin->     on cp.owning_acct_no = fpr.fpr_owning_acct_no
dbadmin->  where cp.owning_acct_no = nm.acct_no
dbadmin->    and sp.product_code = cp.product_code;
ERROR:  relation "cp" does not exist

But if I change the query to only use the ANSI join syntax, I don't get the error:

Code: Select all

dbadmin=> select count(*)
dbadmin->   from cst_customer_product as cp
dbadmin->   join cst_name as nm
dbadmin->     on cp.owning_acct_no = nm.acct_no
dbadmin->   join sys_product as sp
dbadmin->     on sp.product_code = cp.product_code
dbadmin->   left outer join (select * from cst_full_period_rentals fp
dbadmin(>                     where fp.fpr_bill_year = '2012' and fp.fpr_bill_month = '02' ) fpr
dbadmin->     on cp.owning_acct_no = fpr.fpr_owning_acct_no;
 count
-------
     0
(1 row)

Just a thought...

PS. I didn't load the tables with any data.

[dazz]

Hi, Thank you both for your help on this. I updated the code to below, and it seems to be properly extracting the data now, but pls have a look and let me know if Im missing anything. There's one more thing though.. the cst_customer_product table contains multiple rows for each owning_acct_no, serv_no, product_code combination, but I only want it to select the rows with the max instillation date
e.g.
owning_acct_no, serv_no, product_code inst_date cust_prod_status
105036590000 4440270 8641 4/9/2009 W
105036590000 4440270 8641 11/21/2000 Z

So I would only want the first row to be extracted in my query results below. How can this be done?

Thanks again!

select cp.product_code, sp.product_description, cp.unique_serv_id, cp.serv_no,
cp.owning_acct_no, cp.personal_rental, cp.cust_prod_no,
cp.inst_date, cp.price_var_exist, cp.cust_prod_status,
nm.first_name, nm.surname, nm.company_name,
fpr.fpr_bill_year, fpr.fpr_bill_month, fpr.fpr_rental_charge

from cis_prod.cst_customer_product as cp
left outer
join cis_prod.cst_name as nm
on cp.owning_acct_no = nm.acct_no
left outer
join cis_prod.sys_product as sp
on cp.product_code = sp.product_code
left outer
join (select * from cis_prod.cst_full_period_rentals fp
where fp.fpr_bill_year = '2012' and fp.fpr_bill_month = '02' ) fpr
on cp.owning_acct_no = fpr.fpr_owning_acct_no
and cp.cust_prod_no = fpr.fpr_cust_prod_no and
cp.product_code = fpr.fpr_product_code

where (cp.product_code between 8640 and 8649 or cp.product_code between 8660 and 8669 ) and
nm.stop_time_stamp = '31-DEC-9999'

[id10t]

Hi Dazz!

You already answered to your question:

So I would only want the first row to be extracted in my query results below. How can this be done?

Define order to output and with Analytic Function "FIRST_VALUE(<target_column>) OVER (PARTITION BY owning_acct_no, serv_no, product_code ORDER BY inst_date DESC)" select what you need. DESC will promise that MAX(inst_date) is a first value in PARTITION (owning_acct_no, serv_no, product_code).
PS Example for first_value