I aim to list the forth generation of a family using SQL Server.
Example family tree in the picture (id based)
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
Advertisement
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