Finding specific relation in table

Question

I have following problem. I created a table named parentchildrenName. This Table includes the name of a parent and the name of one child in every row. Now i want to return the names of a pairs of parents which have the same child with their childName. This means it should return the name of parent1 and parent2 with the specific childName like this:

  SELECT parentName1, parentName2 childName FROM parentchildren

I am not quit sure how i could make this command work in mysql any help would be appreciated.

Possible duplicate of [How to create a MySQL hierarchical recursive query](https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) — Raymond Nijland, Jun 18 '19 at 11:35
That duplication contains answers with query methods which will work from MySQL 5.1 to MySQL 8 to query child parent relations. — Raymond Nijland, Jun 18 '19 at 11:36

score 0 · Accepted Answer · answered Jun 18 '19 at 12:08

You can use a self join on equal child names but different parent names.

SELECT p.parentname,
       p.childname,
       m.parentname
       FROM parentchildren p
            INNER JOIN parentchildren m
                       ON m.childname = p.childname
                          AND m.parentname < p.parentname;

If you want each parent pair twice, with the parents switching positions you can change < to <>. Or change it to > if you want to have each pair only one but switch the positions in all of the pairs.

Finding specific relation in table

1 Answers1