How to get the third level of a tree in SQL

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

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