CODEWITHSUNDEEP
sqlsqlite
nilrem
nilrem

Reputation: 85

Find the relation in sqlite3

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; of course if they ask me to obtain the father of Gabriel and Gordon the result of the query must be Null because these two children (Gabriel and Gordon) do not have the same father. Is there a way to do this query in sqlite3? Thanks in advance

Upvotes: 1

Views: 507

Answers (2)

forpas
forpas

Reputation: 164099

Create a CTE that returns the names of the persons for which you want the parent.
Join 2 copies of the table persons to the table relations to aggregate and set the condition that all names have a common father in the HAVING clause:

WITH cte(names) AS (VALUES ('George'), ('Gordon')) -- change to the name you want
SELECT p1.names
FROM persons p1
INNER JOIN relations r ON r.id_parente = p1.id
INNER JOIN persons p2 ON p2.id = r.id_child
WHERE p2.names IN cte
GROUP BY p1.id
HAVING COUNT(*) = (SELECT COUNT(*) FROM cte)

Or, without the CTE:

SELECT p1.names
FROM persons p1
INNER JOIN relations r ON r.id_parente = p1.id
INNER JOIN persons p2 ON p2.id = r.id_child
WHERE p2.names IN ('George', 'Gordon')
GROUP BY p1.id
HAVING COUNT(*) = 2 -- the number of persons in the IN list

See the demo.

Upvotes: 1

The Impaler
The Impaler

Reputation: 48810

To get the name of the parent of George you can do:

select p.names 
from person c
join relation r on r.id_child = c.id
join person p on p.id = r.id_parente
where c.names = 'George'

Upvotes: 0

Related Questions