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