SQL query to select only grandchildren

Question

I'm new to SQL, trying to wrap my head around myself, but this is a bit confusing. Here is a simplified version of what I'm working with.

I have this table called people :

 +----+------------+-----------+ | 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 | +----+------------+-----------+ 

The above table is a list of people. The parent_id column indicates who their parents are. If they do not have parents on the table, their parent_id is 0.

Now I want to get separate lists of each group of people: grandparents, children and grandchildren.

It is easy to get grandparents (Larry and Kevin), I can just make this request:

 SELECT name FROM people WHERE parent_id = 0 

But when it comes to getting kids (John, Larry, Jason and Mildred) and grandchildren (Bob and Mabel), I'm lost.

In English, this will be the process of getting the children: “Get all the results from the table. For each, look at their parent ID. Find the result in the table that has their ID. The person’s identifier, and if it is 0, the original person was a child. Add them to the list of what we show. "

For grandchildren, this will be the same as above, but only with an extra step.

It makes sense? How can I write my process above as an SQL query?

Answer 1

This can be solved using a simple JOIN .

To select a list of children:

 SELECT c.name FROM people p JOIN people c ON c.parent_id = p.id WHERE p.parent_id = 0 

To select a list of grandchildren:

 SELECT gc.name FROM people p JOIN people c ON c.parent_id = p.id JOIN people gc ON gc.parent_id = c.id WHERE p.parent_id = 0 

Answer 2

First of all, it is very important to know that this question is very easy to answer if you know that you are working with a fixed set of generations (for example, to grandchildren). If this table ultimately will have many generations, and you want (for example) to find all of Kyle's descendants throughout the family tree, then you are not going to do this with a single query. (I have a stored procedure that deals with arbitrary levels of tree generations.) So for now, let's find grandparents / grandchildren.

As you said, finding grandparents is easy ...

 mysql> select name from people where parent_id = 0; +-------+ | name | +-------+ | Kevin | | Kyle | +-------+ 2 rows in set (0.00 sec) 

Now finding children is not so bad.

Let's find the children of Kyle:

 mysql> select p1.name from people p1 where p1.parent_id in (select p2.id from people p2 where p2.name = 'Kyle'); +-------+ | name | +-------+ | John | | Jason | +-------+ 2 rows in set (0.02 sec) 

And here are Kyle’s grandchildren:

 mysql> select p3.name from people p3 where p3.parent_id in (select p2.id from people p2 where p2.parent_id in (select p3.id from people p3 where p3.name = 'Kyle')); +-------+ | name | +-------+ | Mabel | +-------+ 1 row in set (0.01 sec) mysql> 

Going the other direction ... who is Mabel's parent?

 mysql> select p1.name from people p1 where p1.id = (select p2.parent_id from people p2 where p2.name = 'Mabel'); +-------+ | name | +-------+ | Jason | +-------+ 1 row in set (0.00 sec) mysql> 

... and her grandparents:

 mysql> select p1.name from people p1 where p1.id = (select p2.parent_id from people p2 where p2.id = (select p3.parent_id from people p3 where p3.name = 'Mabel')); +------+ | name | +------+ | Kyle | +------+ 1 row in set (0.00 sec) 

So you can see the pattern that I followed to make these requests if you need great-grandparents and great-grandchildren. However, the resulting query will become cumbersome if you need more generations, and the stored procedure in which the loops are executed will be fine.

The Oracle database has a more elegant solution, an SQL extension called "CONNECT BY PRIOR". For a more detailed reading (and an example of the MySQL stored procedure), check out the Connector for the previous MySQL equivalent here in StackOverflow.

One final note: do yourself a favor if you haven't already done so, and:

 mysql> create index ix_parent_id on people(parent_id); Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> 

This will greatly improve performance for these queries.

Answer 3

To get grandchildren, try this query.

 SELECT name FROM people WHERE parent_id IN (SELECT id from people where parent_id >0); 

Answer 4

This would be easier to understand if you are using a query designer. I put an image of SQL Server view designer. Hope this helps.

Here's how you can find children:

And I suggest you allow null for the parent_id field and use null instead of 0 so that you can establish a relationship from id (like pk) to parent_id (like fk). This will simplify the work of the designer.

Let me know if you also need an example for finding grandchildren.

Answer 5

This is without a JOIN statement, but as a SQL student, I find it easier:

• For grandchildren:

   SELECT grandparent.name AS Grandparents, grandchild.name AS Grandchildren FROM people AS grandparent, people AS parent, people AS grandchild WHERE grandchild.parent_id = parent.id AND parent.parent_id = grandparent.id; 

• For children:

   SELECT parent.name as Parent, child.name AS Child FROM people AS parent, people AS child WHERE child.parent_id = parent.id AND parent.parent_id = 0; 

• And for all child parent pairs:

   SELECT parent.name as Parent, child.name AS Child FROM people AS parent, people AS child WHERE child.parent_id = parent.id; 

It took me a while, but it was fun: D