I am trying to understand someone else's code and here we have a case of nested query (A) that has linkages to the Customer table of the outerquery B.
I am not sure I understand what A is accomplishing. Can I rewite it as:-
( CustomerType X Customer c X Customer_Old ) X CustomerType_Old ctl
where ctl.customer_role_cd = c.code ??
Select (Select ctl.id
from CustomerType_Old ctl
inner join CustomerType ct
on ctl.id = ct.id where ctl.customer_role_cd = c.code ) <------ A
from CustomerType ct
inner join Customer c on c.CustomerType_Id = ct.id
inner join Customer_Old cl on c.id = cl.id
left join CustomerType_Old ctl ON ctl.id = ct.id <----- B
B is :
( CustomerType X Customer X Customer_Old ) Left Join (CustomerType_Old)