multiple cte in single select statement where ctes can refer to each other ...

Question

Expanding on following question (Multiple Select Statement) I would like to know if I can do following:

WITH 
    cte1 as (
      SELECT * from cdr.Location
    ),
    cte2 as (
      SELECT * from cdr.Location
        WHERE cdr.Location.someField = cte1.SomeField
    )
select * from cte1 union select * from cte2

So accent here is on following line:

 WHERE cdr.Location.someField = cte1.SomeField

where within cte2 I'm referencing cte1 ?

Answer 1

Yes, you can reference previously declared CTEs in subsequent CTEs:

WITH cte1 as (
  SELECT t.* 
    FROM cdr.Location t),
     cte2 as (
  SELECT t.* 
    FROM cdr.Location t
    JOIN cte1 c1 ON c1.somefield = t.someField)
SELECT * 
  FROM cte1 
UNION 
SELECT * 
  FROM cte2

NOTES

1. Joining onto cte2 in the cte1 declaration wouldn't work, because the statement is executed from top down.
2. You reference a CTE like any other inline view (which it is) or table/temp table/etc by JOINing on whatever you need.

BTW: Try to formulate a better example in the future - it's good for you and the rest of the SO community who are trying to help you.