CTE for parent child relation with multiple parent

Question

I have a parent child relation table as shown below. I want to retrieve all records for a parent or child ID like all ancestors and parents and if possible with depth. For example I want to find the family of D, it will return the first 14 rows as all are of same family. There may be several set of such family. I want to query with one member and want to get whole family record. Is it possible to implement this using CTE? The family structures as per table record :

                      A
                     / \
                    B   C   G   J
                   /     \ / \ / \
              M   D       E   H   K
             / \ /             \ / \
            N   F               I   L


                 R
                 |
                 S   U
                  \ /
                   T

Please help. The table is like:

   Parent   Child
    ------  ------
    A            B
    A            C
    B            D
    D            F
    M            F
    M            N
    C            E
    G            E
    G            H
    J            H
    J            K
    H            I
    K            I
    K            L
    R            S
    S            T
    U            T

Thanks,

Himadri

Answer 1

I found a solution. But I used CTE in a while loop. If someone has any other solution please suggest. As I have mentioned a table above that contains family records or you can say graphs. Let's name it as tbl_ParentChild.

Here is my code:

Declare @Child varchar(10), @RowsEffected int
Set @Child='D'-----It is the member whose family we want to find

 CREATE Table #PrntChld (Parent varchar(10),Child varchar(10))
 Insert Into #PrntChld
 Select Parent,Child  from tbl_ParentChild MF
 Where MF.Child=@Child or MF.Parent=@Child

 Select @RowsEffected=Count(*) from #PrntChld

 While @RowsEffected>0 
 BEGIN
    ;WITH Prnt(Parent,Child)
        AS
        ( Select M.Parent,M.Child  from tbl_ParentChild M
            Inner Join #PrntChld F On F.Child=M.Child
          UNION ALL
          SELECT e.Parent,e.Child       
              FROM tbl_ParentChild AS E
              INNER JOIN  Prnt AS M                 
                  ON E.Child = M.Parent           
        ),
        PrntChld(Parent,Child)
        AS
        ( Select M.Parent,M.Child  from tbl_ParentChild M
            Inner Join (Select * from Prnt union Select * from #PrntChld) F On M.Parent=F.Parent
          UNION ALL
          SELECT e.Parent,e.Child       
              FROM tbl_ParentChild AS E
              INNER JOIN  PrntChld AS M                 
                  ON M.Child = E.Parent           
        )

    Insert Into #PrntChld
    Select distinct MF.* from PrntChld MF 
    Left Join #PrntChld T On T.Child =MF.Child and T.Parent  = MF.Parent    
    where T.Child is null
    Select @RowsEffected=@@ROWCOUNT

END

Select * from #PrntChld
drop table #PrntChld