SQL: new result row for each join

Question

The table is the following:

|child   | parent|

|John    |       |
|Ivan    | Ralph |
|Kat     | Ivan  |

I need to retrieve by a child name a child-parent entry and a parent-grandparent entry.

e.g. by Kat the result should be:

|Ivan    | Ralph |
|Kat     | Ivan  |

Tried using this query:

SELECT * FROM people AS c1
           LEFT JOIN people AS c2 ON c2.child = c1.parent WHERE c1.child = 'Kat'

But it returns a result as a single row.

|child   | parent| child   | parent|
|Kat     | Ivan  | Ivan    | Ralph |

How I should compose a query so that it will return entires in a new row?

Possible duplicate of [How to create a MySQL hierarchical recursive query](https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) — philipxy, Jun 29 '19 at 20:51
Please in code questions give a [mre]--cut & paste & runnable code; example input with desired & actual output (including verbatim error messages); tags & clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) PS You have an error. You don't understand what some code you added does. Make it clear that your question is about *that error* & ask re your overall goal later in a new post. — philipxy, Jun 29 '19 at 20:53
"retrieve by a child name a child-parent entry and a parent-grandparent entry" is not clear. Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. — philipxy, Jun 29 '19 at 20:56
Which [DBMS](https://en.wikipedia.org/wiki/DBMS) product are you using? "SQL" is just a query language, not the name of a specific database product. Please add the tag for the database product you are using `postgresql`, `oracle`, `db2`, `sql-server`, ... — , Jun 30 '19 at 07:34

forpas · Answer 1 · 2019-06-30T06:48:17.457

1

You don't need a join, a correlated subquery will do:

SELECT * FROM people 
WHERE child = 'Kat' OR
child = (SELECT parent FROM people WHERE child = 'Kat')

assuming that for each child there is only 1 parent.
If there can be more parents then change = to IN:

SELECT * FROM people 
WHERE child = 'Kat' OR
child IN (SELECT parent FROM people WHERE child = 'Kat')

edited Jun 30 '19 at 06:48

answered Jun 29 '19 at 13:50

forpas

If there is no parent for 'Kat', this returns 1 row, the row where child = 'Kat'. – forpas Jun 30 '19 at 06:42

1 Answers1