How to get the third level of a tree in SQL

Question

I aim to list the forth generation of a family using SQL Server.

Example family tree in the picture (id based)

FAMILY TREE

The tables I have are:

Family

+------+-----------------+
| f_id | f_name          |
+------+-----------------+
| 1    | Name Surname 1  |
| 2    | Name Surname 2  |
| 3    | Name Surname 3  |
| 4    | Name Surname 4  |
| 5    | Name Surname 5  |
| 6    | Name Surname 6  |
| 7    | Name Surname 7  |
| 8    | Name Surname 8  |
| 9    | Name Surname 9  |
| 10   | Name Surname 10 |
+------+-----------------+

Relations

+----------+---------+
| r_parent | r_child |
+----------+---------+
| 1        | 2       |
| 1        | 3       |
| 2        | 4       |
| 2        | 5       |
| 3        | 6       |
| 3        | 7       |
| 3        | 8       |
| 7        | 9       |
| 8        | 10      |
+----------+---------+

This is the result I need:

 9 Name Surname
10 Name Surname

Answer 1

You need to do the same join with different aliases to get the last generation The other option is to make recursive CTE.

SELECT 
    G4.*
FROM relations GR1
    INNER JOIN family G1 ON GR1.r_parent = G1.f_id
    INNER JOIN family G2 on GR1.r_child = G2.f_id
    INNER JOIN relations GR2 ON GR2.r_parent = G2.f_id
    INNER JOIN family G3 ON GR2.r_child = G3.f_id
    INNER JOIN relations GR3 ON GR3.r_parent = G3.f_id
    INNER JOIN family G4 ON GR3.r_child = G4.f_id

The script used is:

CREATE TABLE family(f_id int ,f_name nvarchar (200))
INSERT INTO family 
VALUES 
  (1, 'Name Surname 1')
, (2, 'Name Surname 2')
, (3, 'Name Surname 3')
, (4, 'Name Surname 4')
, (5, 'Name Surname 5')
, (6, 'Name Surname 6')
, (7, 'Name Surname 7')
, (8, 'Name Surname 8')
, (9, 'Name Surname 9')
, (10, 'Name Surname 10')

CREATE TABLE relations (r_parent int, r_child int)
INSERT INTO relations VALUES (1, 2), (1, 3), (2,4), (2,5), (3,6), (3,7), (3,8), (7,9), (8,10)



SELECT 
    G4.*
FROM relations GR1
    INNER JOIN family G1 ON GR1.r_parent = G1.f_id
    INNER JOIN family G2 on GR1.r_child = G2.f_id
    INNER JOIN relations GR2 ON GR2.r_parent = G2.f_id
    INNER JOIN family G3 ON GR2.r_child = G3.f_id
    INNER JOIN relations GR3 ON GR3.r_parent = G3.f_id
    INNER JOIN family G4 ON GR3.r_child = G4.f_id


DROP TABLE family
DROP TABLE relations