Self-join with nested select query in T-SQL

Is it possible to join a nested select statement with itself (without writing it out twice and running it twice)

Somethin开发者_开发问答g like this would be ideal

SELECT P.Child, P.Parent, Q.Parent AS GrandParent
FROM (SELECT Child, Parent FROM something-complex) AS P
LEFT JOIN P AS Q ON Q.Child = P.Parent

---

50% possible. You can use a CTE to avoid writing it twice but it will still execute twice.

;WITH p
     AS (SELECT child,
                parent
         FROM   something-complex)
SELECT p.child,
       p.parent,
       q.parent AS grandparent
FROM   p
       LEFT JOIN p AS q
         ON q.child = p.parent  

If the query is expensive you would need to materialize it into a table variable or #temp table to avoid the self join causing two invocations of the underlying query.

---

You could use a common table expression:

WITH P AS (SELECT Child, Parent FROM something-complex)
SELECT P.Child, P.Parent, Q.Parent as GrandParent
LEFT JOIN P AS Q ON Q.Child = P.Parent