Common Table Expression in SQL (with multiple joined CTEs) 

CTE ( common table expression ) can be taken simply as temporary dataset within the query.
It is similar to temporary table but it is not stored as an object like temporary table and is alive
until the query execution.

You can use CTE like a normal table. Complicated joins can be simplified using CTE. Below you can find examples of simple CTE and CTE with joins. These techniques can be used as per your need to get the result data.

Simple CTE

;with cte1 as (

select

       field1,

       field2,

       field3

from dbo.table1

)

select * from cte1

Multiple CTEs with joins 

;with cte1 as (

select 

field1,

field2,

field3

from dbo.table1

), cte2 as (

select 

field4,

field5

from dbo.table2

)

select 

_cte1.*,

_cte2.* 

from cte1 _cte1

inner join cte2 _cte2 on _cte2.field4 = _cte1.field1


For detailed information click the link below :
 https://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx