SELECT in a parent table where ALL children are in the same situation - Mariadb/Mysql

Asked

Viewed 1,147 times

1

I need to select in a parent table, where all children in that 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 table son I have the following fields: filho_id, idade, pai_id

In this example, I need to make a SELECT that lists the parents' names where ALL children are of a certain age, if at least one of the children is not of that age, the father should not appear on the list.

I made the select that 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 father to be listed. I can’t think of a logic that fits, so that the rule applies to ALL children.

mysql sql mariadb

  • a select of all parents who only have twin children ?!

  • That’s right. I only used father and son as an example. But the idea would be this. But not necessarily the FATHER has to have another child. If he has only one child, and the child meets the age criterion, he would already come.

1 answer

1


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

Follows 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 the Not Exists got 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 in Sqlfiddle: http://sqlfiddle.com/#! 9/3c97e5/5

Browser other questions tagged mysql sql mariadb

You are not signed in. Login or sign up in order to post.