Add children rank to SQL result

Question

This is an extension of another question I just asked. I learned there how to properly write a SQL query to get the desired result that I need. But when I tried to apply it to my actual use, I found something else that I need.

This is a simplified version of the table that 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        |
+----+-------------+-----------+

(I added a few more since my last question, just so there's more examples) Now I wanted to be able to get lists of the parents, the children, and the grandchildren. Here's the queries I found to get those:

Grandparents

SELECT name FROM people WHERE parent_id = 0

Children

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

And 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

I'm trying to figure out something for the children, so I'll leave the others for now. That query above for children gives the following result:

+---------+
| name    |
+---------+
| John    |
| Larry   |
| Jason   |
| Mildred |
| Amber   |
| Devon   |
| Alfred  |
| Robert  |
| Keith   |
+---------+

Which is good, but I need it ordered first by alphabetical order of first by the parent, then second by the child. So I add an ORDER BY to my query:

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

Which gives me:

+---------+
| name    |
+---------+
| Amber   |
| Larry   |
| Mildred |
| Devon   |
| Jason   |
| John    |
| Alfred  |
| Keith   |
| Robert  |
+---------+

Good. But now the issue is, I would also like to include the parents in this list, right above where their children are. Additionally, I would like some way to show whether the result is a parent or a child. So I was thinking of another column, called Rank, which would have values of either 1 or 2, to indicate parent or child. So this is the result I want back:

+--------+------+
| name   | rank |
+--------+------+
| Kevin  | 1    |
| Amber  | 2    |
| Larry  | 2    |
| Mildred| 2    |
| Kyle   | 1    |
| Devon  | 2    |
| Jason  | 2    |
| John   | 2    |
| Zack   | 1    |
| Alfred | 2    |
| Keith  | 2    |
| Robert | 2    |
+--------+------+

Does that make sense? What query can I use to get that result?

If it works for you: It is easier to display the parent beside the child in a separate column. YOu would just add "p.name as parent" and get your results. — Norbert, Jul 09 '15 at 22:35
Your three examples of queries to get grandparents, children and grandchildren clearly demonstrate how fast comlexity grows when it comes to graph structures. For this reason, I would highly recommend you to look at such technologies as graph databases, like neo4j — Jacobian, Jul 09 '15 at 22:35
@NorbertvanNobelen Ok, thanks. That works. But I also need to include the parents in the list of names. How could I do that? And for those items, under their "parents" column, I would probably just want it to be "NULL" — eshellborn, Jul 09 '15 at 22:40
YOu can use UNION: SELECT {your children query}, 1 UNION {your parents query}, 2; The 1,2 are your ranks, so really multiple ways of solving the question here :) — Norbert, Jul 09 '15 at 22:41
In your last example, `Mable` and `Bob` are not listed. Is that because they are grandchildren? — Zach Mierzejewski, Jul 09 '15 at 23:12

m.cekiera · Accepted Answer · 2015-07-09T23:39:47.817

Try with this:

SELECT name, 1 as rank FROM people WHERE parent_id = 0
UNION
SELECT child.name AS name, 2 AS rank
FROM people AS parent, people AS child
WHERE child.parent_id = parent.id AND parent.parent_id = 0 
ORDER BY name;

With result:

+---------+------+
| name    | rank |
+---------+------+
| Jason   |    2 |
| John    |    2 |
| Kevin   |    1 |
| Kyle    |    1 |
| Larry   |    2 |
| Mildred |    2 |
+---------+------+

And to add grandchildren, with rank = 3:

SELECT name, 1 as rank FROM people WHERE parent_id = 0
UNION
SELECT child.name AS name, 2 AS rank
FROM people AS parent, people AS child
WHERE child.parent_id = parent.id AND parent.parent_id = 0
UNION
SELECT grandchild.name AS name, 3 AS rank
FROM people AS grandparent, people AS parent, people
AS grandchild WHERE grandchild.parent_id = parent.id
AND parent.parent_id = grandparent.id
ORDER BY rank,name;

With result:

+---------+------+
| name    | rank |
+---------+------+
| Bob     |    3 |
| Jason   |    2 |
| John    |    2 |
| Kevin   |    1 |
| Kyle    |    1 |
| Larry   |    2 |
| Mabel   |    3 |
| Mildred |    2 |
+---------+------+

EDIT

Now it is sorted as you requested, however as you can see, there is additional, but finally hidden, column Family_head:

SELECT T.Name, T.Rank FROM (SELECT name, 1 as Rank, Name AS Family_Head FROM people WHERE parent_id = 0
UNION
SELECT child.name AS Name, 2 AS Rank, 
(SELECT p.name FROM people AS p WHERE child.parent_id = p.id) as Family_Head
FROM people AS parent, people AS child
WHERE child.parent_id = parent.id AND parent.parent_id = 0
ORDER BY Family_head, Rank, Name) AS T;

Result:

+---------+------+-------------+
| name    | Rank | Family_Head |
+---------+------+-------------+
| Kevin   |    1 | Kevin       |
| Amber   |    2 | Kevin       |
| Larry   |    2 | Kevin       |
| Mildred |    2 | Kevin       |
| Kyle    |    1 | Kyle        |
| Devon   |    2 | Kyle        |
| Jason   |    2 | Kyle        |
| John    |    2 | Kyle        |
| Zack    |    1 | Zack        |
| Alfred  |    2 | Zack        |
| Keith   |    2 | Zack        |
| Robert  |    2 | Zack        |
+---------+------+-------------+

Nice. This is good. The only thing is the ordering is wrong. I need the order I mentioned above, show first parent alphabetically, then all its children alphabetically, then second parent, etc. I tried adding ORDER BY parent.name, child.name; but that gave an error — eshellborn, Jul 09 '15 at 23:00
@eshellborn try with `ORDER BY rank,name;` it should give you desired output — m.cekiera, Jul 09 '15 at 23:02
That doesn't work, I need the order to be exactly as above in my post http://sqlfiddle.com/#!9/ed315/22 — eshellborn, Jul 09 '15 at 23:05
@eshellborn so you want a parent, then his children, and again another parent, with his children, etc? — m.cekiera, Jul 09 '15 at 23:07
Ok that works. Now I also need to filter by group_id as I did in my last post, http://stackoverflow.com/questions/31327393/sql-query-to-select-only-grandchildren#comment50641350_31327547. I can't quite figure it out — eshellborn, Jul 10 '15 at 19:48
@eshellborn did you tryied to add AND T.group_id = 5? What is a column group_id? — m.cekiera, Jul 11 '15 at 12:19

jpw · Answer 2 · 2015-07-10T00:01:36.047

The kind of structure you're working with would most likely be much easier to deal with in a graph database or a relational database with better support for recursive queries than MySQL, but using the union all operator together with arithmetic it's possible to achieve a result that is approximately what you're looking for.

SELECT Generation, Name, Rank 
FROM (

    SELECT 'Grand parent' AS Generation, id*1000 level, name, 1 as rank 
    FROM people WHERE parent_id = 0

    UNION ALL

    SELECT 'Parent', p.id*1000 + 1*100, c.name, 2 as rank
    FROM people p
    JOIN people c ON c.parent_id = p.id
    WHERE p.parent_id = 0

    UNION ALL 

    SELECT 'Grand child', p.id*1000+1*100+gc.id, gc.name, 3 as rank
    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

) a 
ORDER BY level, name;
-- WHERE Rank <= 2 -- add this or remove the last union to remove grand children

Gives a result like:

|   Generation |     name | rank |
|--------------|----------|------|
| Grand parent | Kevin    |    1 |
|       Parent | Amber    |    2 |
|       Parent | Larry    |    2 |
|       Parent | Mildred  |    2 |
|  Grand child | Bob      |    3 |
| Grand parent | Kyle     |    1 |
|       Parent | Devon    |    2 |
|       Parent | Jason    |    2 |
|       Parent | John     |    2 |
|  Grand child | Mabel    |    3 |
| Grand parent | Zack     |    1 |
|       Parent | Alfred   |    2 |
|       Parent | Keith    |    2 |
|       Parent | Robert   |    2 |

Sample SQL Fiddle

It's not possible to make that assumption. It doesn't have to be too complicated, I just need some way to differentiate the parents from the children. And it is important that it's in the order I have above. I can't change the structure of the database as its built in to our cms — eshellborn, Jul 09 '15 at 23:02
@eshellborn Have a look now please. I included the grandchild generation, but if you don't want that just comment out the last union all and query. — jpw, Jul 09 '15 at 23:09

Add children rank to SQL result

2 Answers2