Nested SQL, Derived Table in Business Objects Universe, Multiple joins between tables

Though most of the time we deal with simple 1 to 1 join between tables, be it, inner or outer, sometimes relationships between two tables can be based on multiple columns due to the nature of business. I've faced many situations like this in my work.  

For example: A sales area is determined by a BrokerID (has it's own table), Company (has it's own table) and StateID (has it's own table). Now, these 3 fields/columns need to connect to a 4th table to pull proper information about sales area (i.e. Sales Area Number, Sales Reps, Sales Region, Sales regional manager and so on)--and this 4th table also has BrokerID, Company and StateID. In other words, I have to connect 3 columns from 3 different table to this 4th table. It's easy, so don't be scared. :)

In a Business Objects Universe, you could simply drag and join the relationship between tables and break the loops by creating aliases and/or contexts. But, if you ever come across nasty situation like this and want to avoid the never ending aliases and contexts, simply use a derived table. Though this code is for a Crystal Reports, it applies to universe scenario too. 

So, let's see the SQL now:

Select *

From

(

Select 

B.CommonColumn1 , 

B.CommonColumn2 ,

B.Column3 ,

B.Column4 ,

C.CommonColumn5 ,

C.Column6 ,

From TableB as B

Left Join TableC as C (nolock)

On B.Column4 = C.Column6 ,

) A -- This nested query, or query within a query is named as 'A'

-- This table 'A' could also be called a Derived table

-- Double Joins --

Left Join TableD As D (nolock)

On A.CommonColumn1 = D.CommonColumn7

And D.Column8 = 'Y' -- this string 'Y' is just an example

-- basically an additional filter

--- Tripple Joins ---

Left Join TableE as E (nolock)

On D.CommonColumn9 = E.CommonColumn10

And A.CommonColumn2 = E.CommonColumn11

And A.CommonColumn5 = E.CommonColumn12

/*

>>>> A Real Life example could be like the following: >>>>>>

*/

Select       * -- everything

From

-- dbo.CO_Contract AS C  -- Need to connect to 2 (depended) columns from this table 

( 

Select

C2.PropertyAccountID, 

C2.Company,

C2.StateID,

A.StateID,    

  

-- Column 1 is native (C2.Company) – which becomes C.Company

-- and Column 2 is from CO_Address table - A.StateID

-- A.StateID becomes C.StateID, because this entire nested query is named ‘C’ 

From dbo.CO_Contract AS C2

Left Join dbo.CO_Address AS A ON

C2.AddressID = A.CO_ID 

) C -- Again, this is the nested query and it's named as 'C'

-- DOUBLE JOINS --

Left Join dbo.CO_RelatedParty            AS RP

On C.PropertyAccountID = RP.PropertyAccountID AND RP.RE1Flag = 'Y'

--- TRIPPLE JOINS ---

-- joining to ‘BSA’, 2 joins from ‘C’ and one join from ‘RP’

-- In an Universe, query such as this, used in a Derived table, will solve many loops (and traps)

Left Join dbo.CO_BrokerSaleArea BSA

On RP.PartyID = BSA.BrokerID 

And C.Company = BSA.Company 

And C.StateID = BSA.StateID 
 ------------------------------------------------------------------------