Get the Whole List from a many to many relation

Question

i have the following schema

CREATE TABLE BookRelationship
 (Id int IDENTITY(1,1) PRIMARY KEY, Book1 nvarchar(50), Book2 nvarchar(50))

INSERT INTO BookRelationship (Book1,Book2) VALUES
('Book 42','Book 24'), ('Book 42','Book 25'), ('Book 42','Book 56'), ('Book 42','Book 56'), ('Book 43','Book 24'), ('Book 43','Book 25'), ('Book 43','Book 26'), ('Book 43','Book 27'), 
('Book 43','Book 56'), ('Book 44','Book 29'), ('Book 44','Book 30'), ('Book 45','Book 31'), ('Book 45','Book 33'), ('Book 46','Book 34'), ('Book 47','Book 35'), ('Book 48','Book 36'), 
('Book 48','Book 37'), ('Book 49','Book 36'), ('Book 49','Book 37'), ('Book 50','Book 38'), ('Book 50','Book 39'), ('Book 51','Book 38'), ('Book 51','Book 39'), ('Book 52','Book 40'), 
('Book 52','Book 41'), ('Book 53','Book 40'), ('Book 53','Book 41'), ('Book 57','Book 24'), ('Book 57','Book 25'), ('Book 57','Book 26'), ('Book 57','Book 27'), ('Book 57','Book 56'), 
('Book 63','Book 24'), ('Book 63','Book 25'), ('Book 63','Book 26'), ('Book 63','Book 27'), ('Book 63','Book 56'), ('Book 63','Book 59'), ('Book 64','Book 59'), ('Book 64','Book 61'), 
('Book 65','Book 61'), ('Book 65','Book 62')

these are relations between different books

Id	Book1	Book2
1	Book 42	Book 24
2	Book 42	Book 25
3	Book 42	Book 56
4	Book 42	Book 56
5	Book 43	Book 24
6	Book 43	Book 25
7	Book 43	Book 26
8	Book 43	Book 27
9	Book 43	Book 56
10	Book 44	Book 29
11	Book 44	Book 30
12	Book 45	Book 31
13	Book 45	Book 33
14	Book 46	Book 34
15	Book 47	Book 35
16	Book 48	Book 36
17	Book 48	Book 37
18	Book 49	Book 36
19	Book 49	Book 37
20	Book 50	Book 38
21	Book 50	Book 39
22	Book 51	Book 38
23	Book 51	Book 39
24	Book 52	Book 40
25	Book 52	Book 41
26	Book 53	Book 40
27	Book 53	Book 41
28	Book 57	Book 24
29	Book 57	Book 25
30	Book 57	Book 26
31	Book 57	Book 27
32	Book 57	Book 56
33	Book 63	Book 24
34	Book 63	Book 25
35	Book 63	Book 26
36	Book 63	Book 27
37	Book 63	Book 56
38	Book 63	Book 59
39	Book 64	Book 59
40	Book 64	Book 61
41	Book 65	Book 61
42	Book 65	Book 62

i want to get all the recursive related books from book1 and book2 for a given book

For Example

for given Book 57

we get all book1 and book2 related to 57

Select Book1 From BookRelationship WHERE Book2 = 57 
UNION
Select Book2 From BookRelationship WHERE Book1 = 57

then by Recursive

    Select Book1 
    From BookRelationship 
    WHERE Book2 in (
            Select Book1 From BookRelationship WHERE Book2 = 'Book 57' 
            UNION 
            Select Book2 From BookRelationship WHERE Book1 = 'Book 57' ) 
    UNION
    Select Book2 
    From BookRelationship 
    WHERE Book1 in (
            Select Book1 From BookRelationship WHERE Book2 = 'Book 57' 
            UNION 
            Select Book2 From BookRelationship WHERE Book1 = 'Book 57' )

then recursively we get the whole related list of books related to each other

Select Book1 
From BookRelationship 
WHERE Book2 in (
                Select Book1 
                From BookRelationship 
                WHERE Book2 in (
                        Select Book1 From BookRelationship WHERE Book2 = 'Book 57' 
                        UNION 
                        Select Book2 From BookRelationship WHERE Book1 = 'Book 57' ) 
                UNION
                Select Book2 
                From BookRelationship 
                WHERE Book1 in (
                        Select Book1 From BookRelationship WHERE Book2 = 'Book 57' 
                        UNION 
                        Select Book2 From BookRelationship WHERE Book1 = 'Book 57' ) 
                ) 
UNION
Select Book2 
From BookRelationship 
WHERE Book1 in (
                Select Book1 
                From BookRelationship 
                WHERE Book2 in (
                        Select Book1 From BookRelationship WHERE Book2 = 'Book 57' 
                        UNION 
                        Select Book2 From BookRelationship WHERE Book1 = 'Book 57' ) 
                UNION
                Select Book2 
                From BookRelationship 
                WHERE Book1 in (
                        Select Book1 From BookRelationship WHERE Book2 = 'Book 57' 
                        UNION 
                        Select Book2 From BookRelationship WHERE Book1 = 'Book 57' ) 
                )

and so on till we get the whole list

i need only the books that are related recursively to 'Book 57' for example .. they are 'Book 24', 'Book 25', 'Book 26', 'Book 27', 'Book 42', 'Book 43', 'Book 56', 'Book 57', 'Book 59', 'Book 61', 'Book 62', 'Book 63', 'Book 64', 'Book 65'

hope i can explain well

Is there a possibility of a cycle in the data, e.g. `'Book 1'` → `'Book 2'` → `'Book 4'` → `'Book 2'` → `'Book 4'` → ... ? — HABO, Jan 14 '21 at 15:27
It is not a tree hierarchy but it is a many to many relation with no conditions — Mariam Nagy, Jan 14 '21 at 18:54
Does this answer your question? [How to find all connected subgraphs of an undirected graph](https://stackoverflow.com/questions/35254260/how-to-find-all-connected-subgraphs-of-an-undirected-graph) — Sabito stands with Ukraine, Jan 15 '21 at 11:09

score 1 · Answer 1 · answered Jan 15 '21 at 02:11

To build this kind of relation you need first to restructure data it self In two table 1- first table contain only “books “ and Id for each book 2- second one contain relation between each book Include recursive relation This will help you to write performance query without any Complexity
The important question What is the business case for the problem

score 0 · Answer 2 · answered Jan 14 '21 at 14:08

0

Somtehing like :

WITH 
T_REC AS
(SELECT Book1 AS BookName, Book2
 FROM BookRelationship
 UNION ALL
 SELECT T.Book2, R.Book2
 FROM   BookRelationship AS T JOIN T_REC AS R
           ON T.Book1 = R.Book2)
SELECT DISTINCT Book2 FROM T_REC;

answered Jan 14 '21 at 14:08

SQLpro

3,994
1
6
14

this returns all the books but i need only the books that are related recursively to 'Book 57' for example .. they are 'Book 24', 'Book 25', 'Book 26', 'Book 27', 'Book 42', 'Book 43', 'Book 56', 'Book 57', 'Book 59', 'Book 61', 'Book 62', 'Book 63', 'Book 64', 'Book 65' – Mariam Nagy Jan 14 '21 at 19:14

Get the Whole List from a many to many relation

2 Answers2