CODEWITHSUNDEEP
mysqlsql
eshellborn
eshellborn

Reputation: 11261

SQL query to select only entries that have grandchildren

Yesterday I asked this question about selecting only the grandchildren entries. It took me a while but I finally figured out how to do what I want.

Now, though, I need to do something else: select only grandparent entries (by "grandparent", I mean people that don't have a parent, ie. their parent_id is 0), but only ones that have a grandchild, regardless of whether or not they have a child.

This is the table I have:

+-----+-------------+-----------+
| id  | name       | parent_id |
+-----+------------+-----------+
|  1  | Bob        | 3         |
|  2  | John       | 5         |
|  3  | Larry      | 4         |
|  4  | Kevin      | 0         |
|  5  | Kyle       | 0         |
|  6  | Jason      | 5         |
|  7  | Mildred    | 4         |
|  8  | Mabel      | 6         |
|  9  | Amber      | 4         |
|  10 | Devon      | 5         |
|  11 | Zack       | 0         |
|  12 | Alfred     | 11        |
|  13 | Robert     | 11        |
|  14 | Keith      | 11        |
|  15 | David      | 0         |
|  16 | Wilbur     | 15        |
+-----+------------+-----------+

And this is the result I want back:

+---------+
| name    |
+---------+
| Kevin   |
| Kyle    |
+---------+

Only those three parents. I don't want David or Zack in the result, because they have no grandchildren.

To get all the grandparents, I could simply do:

SELECT name FROM people WHERE parent_id = 0

But I need a query that can only select entries that have a parent id of zero and that are grandparents.

(http://sqlfiddle.com/#!9/b1a818/3)

Upvotes: 0

Views: 248

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

I think you need a double self-JOIN so as to reach grandchildren level:

SELECT DISTINCT p1.name 
FROM people AS p1
INNER JOIN people AS p2 ON p1.id = p2.parent_id
INNER JOIN people AS p3 ON p2.id = p3.parent_id
WHERE p1.parent_id = 0

INNER JOINs will filter out any records that don't reach two levels down the parent-child hierarchy.

Demo here

Upvotes: 1

Related Questions