How to join a subquery to itself?

Question

How do can you join a subquery onto itself? I'd like to do something like the following.

SELECT 
    four.src AS start, four.dest AS layover, f.dest AS destination
FROM 
    ( SELECT 1 AS src, 2 as dest union all select 2, 3 ) AS four
JOIN
    four AS f 
ON f.src = four.dest 

However the query above gives me the error

Msg 208, Level 16, State 1, Line 1 Invalid object name 'four'.

I'd rather not have to store it as a variable or view etc first since this is part of a monolithic query (this is itself a subquery and its part of a series of UNIONS) and I do not want to make sure that there are no impacting joins elsewhere that relate.

The force behind this change is that fourused to be a simple lookup but now for this query the values have to be calculated.

---

PS - this is a simplified example, in my case the subquery for four is a hundred lines long

Answer 1

You can make use of CTE (Common Table Expression in this scenario. Here, you need not to store this result in any temporary objects.

;WITH four AS (
SELECT 1 AS src, 2 as dest 
union all 
select 2, 3
)

SELECT F1.src AS start, F1.dest AS layover, f2.dest AS destination
FROM four F1
INNER JOIN four F2 ON F1.src = F2.dest 

Answer 2

Use a temp table.

Declare @Temp(src int, desc int);
INSERT INTO @Temp(src,desc)
VALUES
(SELECT 1 AS src, 2 as dest union all select 2, 3)
SELECT * FROM @Temp t1
INNER JOIN @Temp t2 ON t1.src = t2.dest