SELECT on a parent table where ALL children are in the same situation - MariaDB / MySQL

Question

SELECT on a parent table where ALL children are in the same situation - MariaDB / MySQL

Navigation

#1 by (1 votes)

1

I need to make a select in a parent table, where all the children in this table NEED to be in the same situation.

For example:

In the parent table I have the following fields: pai_id, pai_nome

In the child table I have the following fields: filho_id, idade, pai_id

In this example, I need to make a SELECT that lists the names of the parents where TODOS the children have a certain age, if at least one of the children is not that age, the parent should not appear in the list .

I made the select this way:

SELECT DISTINCT
pai_nome
FROM pai p
INNER JOIN filho f ON (f.pai_id = p.pai_id)
WHERE f.idade = 15

But in this case, only if one of the children meets the age criterion is enough for the parent to be listed. I can not think of a logic that meets, so that the rule applies to ALL children.

mysql sql mariadb

asked by anonymous 12.11.2017 / 02:33

1 answer

Convert html5 video time in hours, minutes and seconds - JavaScript Dictionary running all keys instead of just one

score 1 · Accepted Answer

First you select everyone who has children of age = 14 (for example), and then removes everyone who has a child other than age 14.

Follow the code:

SELECT DISTINCT
    p.pai_id,
    p.pai_nome
FROM pai p
INNER JOIN filho f ON (f.pai_id = p.pai_id) and f.idade = 14
where p.pai_id not in (select x.pai_id from filho x where x.idade != f.idade and x.pai_id = p.pai_id);

Using Not Exists was even faster:

SELECT DISTINCT
    p.pai_id,
    p.pai_nome
FROM pai p
INNER JOIN filho f ON (f.pai_id = p.pai_id) and f.idade = 14
where not exists (select 1 from filho x where x.idade != f.idade and x.pai_id = p.pai_id);

I put it in SQLFiddle: link