Find All Parents based on certain record

Question

I need some helps about SQL code. I have 2 tables, the first one is table name

NameID   Name      
1          John          
2          Paul          
3          Jessica          
4          Nancy          
5          Sam          
6          Jane
7          Jimmy

The second one is table Family

 FamilyID   NameID    ChildID
    1          1           2
    2          1           3
    3          2           4
    4          3           5
    5          3           6
    6          5           7

Field "NameID" and "ChildID" in table Family are connected to field "NameID" in table Name. So if I put it in a tree it will be like this

         John
         /  \
      Paul  Jessica
      /       /  \
   Nancy    Sam  Jane
            /
          Jimmy

What I need is SQL code that can find "All" Parents for certain record. For example :

1. I want to know all parents from Jane, the results will be : Jessica, John

2. I want to know all parents from Jimmy, the results will be : Sam, Jessica, John

3. I want to know all parents from Nancy, the results will be : Paul, John

Answer 1

Here you go, Use a recursive CTE as follows:

  DECLARE @pName VARCHAR(20)
  SET @pName = 'Jane'

  ;WITH  RecursiveFamilyCTE
          AS (
               SELECT
                ParentName.NAME,
                ParentName.NameID,
                f.ChildID
               FROM
                dbo.Family AS f
                JOIN NAME AS ChildName
                  ON f.ChildID = ChildName.NameID
                JOIN Name AS ParentName
                  ON f.NameID = ParentName.NameID
               WHERE
                 ChildName.NAME = @pName

               UNION ALL

               SELECT 
                ParentName.NAME,
                ParentName.NameID,
                f.ChildID
               FROM
                dbo.Family AS f
                JOIN NAME AS ChildName
                  ON f.ChildID = ChildName.NameID
                JOIN Name AS ParentName
                  ON f.NameID = ParentName.NameID
                JOIN RecursiveFamilyCTE
                  ON f.ChildID = RecursiveFamilyCTE.NameID
             )
    SELECT
      NAME
    FROM
      RecursiveFamilyCTE