SQL query to chain of relationships between two persons

Question

I have a database with three tables:

relation_type table

id  relation_title
----------------------
1   father
2   mother
3   son
4   daughter
5   husband
6   wife

users table

id  user_name
----------------------
1   amr ali
2   ali saad
3   amal samir
4   sara said
5   said mohammed
6   mona ashraf

relationships table

id  first_id    second_id   relation_type_id
---------------------------------------------------------
1   1           2           3         
2   2           1           1         
3   1           3           3         
4   3           1           2         
5   1           4           5         
6   4           1           6         
7   4           5           4         
8   5           4           1         
9   5           6           5         
10  6           5           6

When I run the query I get the result below:

SELECT u1.user_name,
       u2.user_name,
       r_t.relation_title
FROM relationships r
JOIN relation_type r_t ON r.relation_type_id = r_t.id
JOIN users u1 ON r.first_id = u1.id
JOIN users u2 ON r.second_id = u2.id
WHERE first_id = 1

================================================
user_name       user_name       relation_title
------------------------------------------------
amr ali         ali saad        son
amr ali         amal samir      son
amr ali         sara said       husband

What I want to get when I want to draw the chain of relationships between two persons who are not directly related with relationship, like "amr ali" and "mona ashraf" ... How do I get this the following figure via a SQL query

================================================
user_name       user_name       relation_title
------------------------------------------------
amr ali         sara said       husband
sara said       said mohammed   daughter
said mohammed   mona ashraf     husband

Answer 1

You could try using a recursive CTE, you have to be restrictive in the first part of the CTE to avoid recursion loops, the second where is to try to restrict the relationships so you don't get for instance both son and mother:

SQL Fiddle

MS SQL Server 2017 Schema Setup:

create table relation_type
(
   ID int PRIMARY KEY,
   Relation_Title varchar(20)
);

CREATE TABLE users
(
   ID int PRIMARY KEY,
   user_name varchar(20)
);

CREATE TABLE relationships
(
    ID int PRIMARY KEY,
    first_id int,
    second_id int,
    relation_type_id int
 );
 
 INSERT INTO relation_type
 VALUES (1, 'father'),
        (2, 'mother'),
        (3, 'son'),
        (4, 'daughter'),
        (5, 'husband'),
        (6, 'wife');
        
INSERT INTO users
VALUES (1, 'amr ali'),
       (2, 'ali saad'),
       (3, 'amal samir'),
       (4, 'sara said'),
       (5, 'said mohammed'),
       (6, 'mona ashraf');
       
INSERT INTO relationships
VALUES (1,1,2,3),
       (2,   2,           1,           1),
       (3,   1,           3,           3),
       (4,   3,           1,           2),
       (5,   1,           4,           5),
       (6,   4,           1,           6),
       (7,   4,           5,           4),
       (8,   5,           4,           1),
       (9,   5,           6,           5),
       (10,  6,           5,           6);

Query 1:

WITH CTE
AS
(
  SELECT u1.user_name first_user_name,
       u2.user_name second_user_name,
       r_t.relation_title,
       r.first_id,
       r.second_id,
       0 as lvl
  FROM relationships r
  JOIN relation_type r_t ON r.relation_type_id = r_t.id
  JOIN users u1 ON r.first_id = u1.id
  JOIN users u2 ON r.second_id = u2.id
  WHERE r.first_id = 1
  UNION ALL
  SELECT u1.user_name first_user_name,
       u2.user_name second_user_name,
       r_t.relation_title,
       r.first_id,
       r.second_id,
       c.lvl + 1
  FROM relationships r
  JOIN relation_type r_t ON r.relation_type_id = r_t.id
  JOIN users u1 ON r.first_id = u1.id
  JOIN users u2 ON r.second_id = u2.id
  JOIN CTE C on C.second_id = r.first_Id
  WHERE 
       ((C.Relation_Title IN ('father','mother') 
         AND r_t.Relation_Title not in ('son','daughter')) OR
        (C.Relation_Title IN ('son','daughter') 
         AND r_t.Relation_Title not in ('father','mother')) OR
        (C.Relation_Title = 'husband' 
         AND r_t.Relation_Title <> 'wife') OR
        (C.Relation_Title = 'wife' 
         AND r_t.Relation_Title <> 'husband'))

)
SELECT *
FROM CTE

Results:

| first_user_name | second_user_name | relation_title | first_id | second_id | lvl |
|-----------------|------------------|----------------|----------|-----------|-----|
|         amr ali |         ali saad |            son |        1 |         2 |   0 |
|         amr ali |       amal samir |            son |        1 |         3 |   0 |
|         amr ali |        sara said |        husband |        1 |         4 |   0 |
|       sara said |    said mohammed |       daughter |        4 |         5 |   1 |
|   said mohammed |      mona ashraf |        husband |        5 |         6 |   2 |