Google Interview Question: Recursive Query or Common Table Expression for the following scenario

I have two tables TableA and TableB in the following fashion:

Table A(ID, PairId)
--Here the Pair represented by PairId will always have 2 elements in it.

Data:
100,1
101,1
----- 
104,2
109,2



TableB(A.ID, GroupId)
--Here the Group represented by GroupId will may have any number of elements. 
--Also, A.ID means its a foriegn key from TableA
Data:
100,1000
102,1000
103,1000
--------
101,1001
104,1001
105,1001
-------
105,1002
106,1002
107,1002

Given an id from table A (say X), Find the ids of its pairmates, and then the groupmates(of the previous pairmates) and then the pairmates(of all the ones we found in previous step) and their groupmates (of all the ones we found in previous step) and so on.... until you dont find any pairmates or groupmates.

For instance, given X as 100
you will accumulate data in this fashion:

Include PairMates
100
101

开发者_高级运维Include GroupMates(of all the ones in prevstep)
100--groupmates of 100
102
103
101--groupmates of 101
104
105

Include PairMates(of all the ones in the prevstep)
100
102
103
101
104--Pairmate of 104
109
105

Include Groupmates(of all the ones in the prev step)
100
102
103
101
104
109
105--Groupmates of 105
106
107

Include Pairmates( of all the ones in the prevstep)
100
102
103
101
104
109
105
106
107
[None found]
Include Groupmates( of all the ones in the prevstep)
100
102
103
101
104
109
105
106
107
[Nonefound]
---since no pairmates and groupmates were added so the recursion ends

DECLARE @ID INT  = <Given ID>

WITH CTE AS
(
SELECT * FROM TABLEB 
WHERE ID IN (SELECT ID FROM TABLEA WHERE pairID IN (SELECT pairId FROM TABLEA WHERE ID = @ID)) 
UNION 
SELECT * FROM TABLEB WHERE GroupID IN (SELECT GroupID FROM CTE)
)
SELECT * FROM CTE