1

In this example, two tables define the groups and the relationship of persons within each group

CREATE TABLE groups
(
group_id int(11) unsigned NOT NULL,
person_id int(11) unsigned NOT NULL,
INDEX(person_id),
PRIMARY KEY(group_id,person_id)
) ENGINE=InnoDB

CREATE TABLE relationships
(
person1 int(11) unsigned NOT NULL,
person2 int(11) unsigned NOT NULL,
INDEX(person2),
PRIMARY KEY(person1,person2)
) ENGINE=InnoDB

How can I find the inner circles (sub-groups)?

For the given example,

INSERT INTO groups (group_id,person_id) VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(2,8),(2,9),(2,10)

INSERT INTO relationships (person1,person2) VALUES (1,2),(1,3),(3,5),(7,6),(9,10)

the expected output is:

group    sub_group_id person
1        1            1
1        1            2
1        1            3
1        1            5
1        2            4
1        3            7
1        3            6
2        1            8
2        2            9
2        2            10

sub_group_id is an arbitrary value assigned to distinguish between the sub-groups.

1
  • Build all trees with recursive CTE and enumerate them using the relationship table partitioning by the root. Then join groups table by the presence in a tree.
    – Akina
    Jul 29 at 6:01

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.