SQLite Forum

Find the relation in a sql query

Find the relation in a sql query

(1) By tronar on 2021-05-25 10:50:53 [link] [source]


I have this table that gives the names of the father and the children:

id names
1 Frank
2 Gabriel
3 Geoffrey
4 George
5 Gordon
6 Heather

This second table tells who the children of each parent are, for example Geoffrey and Gabriel are Frank's children, and George, Gordon and Heather are Geoffrey's children:

id id_parente id_child
1 1 2
2 1 3
3 3 4
4 3 5
5 3 6

I have tried in various ways to find a way to make a query that gives me the name of the father given the name of one or more children, for example if they give me George and Gordon the query should give me Geoffrey. Is there a way to do this query? Thanks in advance

(2) By Kees Nuyt (knu) on 2021-05-25 11:27:35 in reply to 1 [source]

The SQLite documentation contains an example of traversing a tree structure , which is much like your requirement.

Not exactly the same, but close enough to inspire you.

(4) By tronar on 2021-05-25 15:42:38 in reply to 2 [link] [source]

Thank you very much. "Give a man a fish, and you feed him for a day; teach a man to fish, and you feed him for life'. "

(3) By Igor Tandetnik (itandetnik) on 2021-05-25 13:21:09 in reply to 1 [link] [source]

Something along these lines:

select distinct p.id, p.names
from Names p join Relation r on (p.id=r.id_parente)
     join Names c on (c.id=r.id_child)
where c.names in ('George', 'Gordon');

(5) By tronar on 2021-05-25 15:43:33 in reply to 3 [link] [source]

Thank you very much, it has been very useful to me.