1

This is a part of my query:

LEFT OUTER JOIN hz_cust_acct_sites_all acct ON acct.party_site_id = hzps.party_site_id 

LEFT OUTER JOIN hz_cust_site_uses_all site ON site.cust_acct_site_id = acct.cust_acct_site_id
                                          AND site.site_use_code = 'BILL_TO'

Is it OK to reference acct in the second LEFT OUTER JOIN statement? How does scope work for SQL.

Thanks!

Kermit
  • 33,827
  • 13
  • 85
  • 121
Caffeinated
  • 11,982
  • 40
  • 122
  • 216

1 Answers1

1

Yes, it is technically allowed to have optional part of optional part

EDIT: Still I am in doubt I understood question correctly. I guessed it is about OUTER JOIN chaining.

Consider this example: 

create table t1 ( a int, b int ); 
create table t2 ( a int, b int ); 
create table t3 ( a int, b int ); 

insert into t1 values ( 1, 10 ); 
insert into t1 values ( 2, 20 ); 
insert into t1 values ( 3, 30 ); 

insert into t2 values ( 10, 100 ); 
insert into t2 values ( 20, 200 ); 

insert into t3 values ( 100, 1000 ); 
insert into t3 values ( 300, 3000 );

Below query 

select t1.a, t2.b t2_b, t3.b t3_b
from 
  T1 
    left join t2 on ( t1.b = t2.a ) 
    left join t3 on ( t2.b = t3.a ) 
order by 1, 2, 3

would get: 

A   T2_B    T3_B
1   100 1000    ==> here all linked.OK
2   200 (null)  ==> here just T2_B got, but no link to T3. 
3   (null)  (null)  ==> no elements to bring from T2, T3

Also as http://sqlfiddle.com/#!4/f61f6/5

igr
  • 3,409
  • 1
  • 20
  • 25