sgmunson (7/10/2015)
Luis Cazares (4/22/2015)
This can be done with a recursive CTE. You can find many examples on the internet.It should be something like this:
WITH RCTE AS(
select T1.a1, T1.a2, T1.a3,
T2.b1, T2.b2, T2.b3, T2.b4
from T1
JOIN T2 ON T1.a2=T2.a2
WHERE T1.a1 IS NULL
UNION ALL
select T1.a1, T1.a2, T1.a3,
T2.b1, T2.b2, T2.b3, T2.b4
from T1
JOIN T2 ON T1.a2=T2.a2
JOIN RCTE ON T1.a1 = RCTE.a2
)
SELECT *
FROM RCTE;
Don't forget that recursive CTEs have a 100 recursion limit unless you override it. To do so, specify this after your final query:
OPTION (MAXRECURSION n)
Substitute a positive value for n that is up to 32767. Here's the text from BOL:
MAXRECURSION number
Specifies the maximum number of recursions allowed for this query. number is a non-negative integer between 0 and 32767. When 0 is specified, no limit is applied. If this option is not specified, the default limit for the server is 100.
I just want to note that if you hit the default limit, you might be doing something wrong. That level of recursion will be very painful for performance.