How does one find a model who is not only the only-child but only-cousin?-CodePudding

I have a Grandparent -> Parent -> Child relationship of separate models.

I am trying to make a SQL statement to return all Children who are the only Child of both their Parents and any other Parents of the same Grandparent.

The closest I can get to this is the following query, which does not work but perhaps will help clarify my purpose :

 SELECT 
   COUNT(*) AS count_all, 
   grandparents.id AS grandparents_id 
FROM `childs` 
INNER JOIN `grandparents` ON `grandparents`.`id` = `childs`.`grandparents_id` 
INNER JOIN `parents` ON `parents`.`id` = `childs`.`parents_id` 
GROUP BY grandparents.id 
HAVING (count("parents.id") > 1)

But this query unfortunately only grabs some of the correct response ( in my case 1 ), not all of them. Any idea how I can do this query correctly?

CodePudding user response：

I think you can do it just by using the child table like so:

select c.*
from childs c
join (
    select grandparents_id 
    from childs
    group by grandparents_id
    having count(*) = 1
) t on t.grandparents_id = c.grandparents_id

Btw the plural form of child is children not childs. Edit: Your current table layout does not allow for a child to have more than one parent and one grandparent.