Tree-Hierarchical query without stored procedure

Question

I am using MySQL, is it possible to get following result in single SQL statement from below table structure?

Currently I am able to get same result by using logical while loop in my PHP code. It will be good if I can achieve in a single SQL, for performance.

Expected result:

|----------+-----------------------------------------------+
|  Id (PK) +    headerANDsubheader                         + 
|----------+-----------------------------------------------+
|  1       +    A-head                                     +
|  4       +     -A-head-A1-subHead                        +
|  5       +     -A-head-A2-subHead                        +
|  6       +      --A-head-A1-subHead-A1.1-subHead         +
|  7       +      --A-head-A1-subHead-A1.2-subHead         +

Column Id is primary key. If parent key is 0 then it means, its root level heading.

If ParentKey is not equal to 0 then it means it a sub heading of someone, and ParentKey is a pointer for that.

Table: Header_sub

|----------+-----------------------------------------------+------------+
|  Id (PK) +    headerANDsubheader                         + ParentKey  +
|----------+-----------------------------------------------+------------+
|  1       +    A-head                                     +   0        +
|----------+-----------------------------------------------+------------+
|  2       +    B-head                                     +   0        +
|----------+-----------------------------------------------+------------+
|  3       +    C-head                                     +   0        +
|----------+-----------------------------------------------+------------+
|  4       +    A-head-A1-subHead                          +   1        +
|----------+-----------------------------------------------+------------+
|  5       +    A-head-A2-subHead                          +   1        +
|----------+-----------------------------------------------+------------+
|  6       +    A-head-A1-subHead-A1.1-subHead             +   4        +
|----------+-----------------------------------------------+------------+
|  7       +    A-head-A1-subHead-A1.2-subHead             +   4        +
|----------+-----------------------------------------------+------------+

I am trying like this ...

SELECT 
    CONCAT(REPEAT(' ', (COUNT(parent.subject_group_name) - 1) ), node.subject_group_name) AS name
FROM 
    major_scholastic as node,
    major_scholastic as parent
WHERE 
    node.s_gid BETWEEN parent.s_gid AND parent.parent_id
GROUP BY  node.subject_group_name
ORDER BY node.s_gid

Why don't your table and column names from query and sample data match? — Paul Spiegel, Aug 07 '16 at 14:47
If header_sub is your actual table structure: why not just add the depth as a column when you update the path and use this in `repeat`? — Solarflare, Aug 07 '16 at 14:51

Paul Spiegel · Accepted Answer · 2016-08-07T18:41:06.330

Your headerANDsubheader column is some kind of meterialized path. That allows you to get all ancestors (not only the direct parent) by using a JOIN with a LIKE condition.

The following query demonstrates how to get the information you might need for different tasks:

select node.*
    , group_concat(anc.Id order by char_length(anc.headerANDsubheader)) as idPath
    , group_concat(lpad(anc.Id, 10, 0) order by char_length(anc.headerANDsubheader)) as idPathSortable
    , count(anc.Id) as depthLevel
    , concat(repeat('- ', count(anc.Id)-1), node.headerANDsubheader) as indendtedHeader
from header_sub node
join header_sub anc
    on node.headerANDsubheader like concat(anc.headerANDsubheader, '%')
group by node.Id
order by idPathSortable

The result looks like this:

| Id |             headerANDsubheader | ParentKey | idPath |                   idPathSortable | depthLevel |                    indendtedHeader |
|----|--------------------------------|-----------|--------|----------------------------------|------------|------------------------------------|
|  1 | A-head                         |         0 | 1      | 0000000001                       |          1 | A-head                             |
|  4 | A-head-A1-subHead              |         1 | 1,4    | 0000000001,0000000004            |          2 | - A-head-A1-subHead                |
|  6 | A-head-A1-subHead-A1.1-subHead |         4 | 1,4,6  | 0000000001,0000000004,0000000006 |          3 | - - A-head-A1-subHead-A1.1-subHead |
|  7 | A-head-A1-subHead-A1.2-subHead |         4 | 1,4,7  | 0000000001,0000000004,0000000007 |          3 | - - A-head-A1-subHead-A1.2-subHead |
|  5 | A-head-A2-subHead              |         1 | 1,5    | 0000000001,0000000005            |          2 | - A-head-A2-subHead                |
|  2 | B-head                         |         0 | 2      | 0000000002                       |          1 | B-head                             |
|  3 | C-head                         |         0 | 3      | 0000000003                       |          1 | C-head                             |

sqlfiddle

Now having that it's a small step to get the result you need:

select node.Id, concat(
    repeat(' ', count(anc.Id)-1),
    repeat('-', count(anc.Id)-1),
    node.headerANDsubheader
) as indendtedHeader
from header_sub node
join header_sub anc
    on node.headerANDsubheader like concat(anc.headerANDsubheader, '%')
group by node.Id
order by group_concat(lpad(anc.Id, 10, 0) order by char_length(anc.headerANDsubheader))

Result:

| Id |                    indendtedHeader |
|----|------------------------------------|
|  1 | A-head                             |
|  4 |  -A-head-A1-subHead                |
|  6 |   --A-head-A1-subHead-A1.1-subHead |
|  7 |   --A-head-A1-subHead-A1.2-subHead |
|  5 |  -A-head-A2-subHead                |
|  2 | B-head                             |
|  3 | C-head                             |

sqlfiddle

Update:

join header_sub anc
    on node.headerANDsubheader like concat(anc.headerANDsubheader, '%')

anc is a shortcut for "ancestor". We want to join every node with all its ancestors (including itself). The condition can be read as anc.headerANDsubheader IS PREFIX OF node.headerANDsubheader. So "A-head-A1-subHead-A1.1-subHead" will be joined with "A-head", "A-head-A1-subHead" and "A-head-A1-subHead-A1.1-subHead". Grouping the result by node.id we can use the aggregations COUNT to get the depth level and GROUP_CONCAT to generate a usefull path. However it would be better to store the depth and path in the table, so we wouldn't need the join at all.

Thanks for your effort. But what if my headerANDsubheader like below. If I change data as below its not working. Can it be on Id rather than headerANDsubheader value? INSERT INTO Header_sub (`Id`, `headerANDsubheader`, `ParentKey`) VALUES (1, 'A head', 0), (2, 'B head', 0), (3, 'C head', 0), (4, 'subHead', 1), (5, 'A2 subHead', 1), (6, 'A1.1 subHead', 4), (7, 'A1.2 subHead', 4) — fresher, Aug 07 '16 at 16:58
Then it will not work. You either add a column to your table which will store the path (like `idPath` or better `idPathSortable ` in my example) or you will need a (recursive) PHP solution. [Here](http://stackoverflow.com/a/37254368/5563083) you can find such a function (last part), which creates a nested HTML list using only one simple SQL query. — Paul Spiegel, Aug 07 '16 at 17:42
Ok, got your point. Can you let me know how this part of query works ... join header_sub anc on node.headerANDsubheader like concat(anc.headerANDsubheader, '%'). Why LIKE concat(anc.headerANDsubheader, '%'), why not = instead of like — fresher, Aug 07 '16 at 18:05
See the **Update**. `=` would only join every node with itself. — Paul Spiegel, Aug 07 '16 at 18:41

Tree-Hierarchical query without stored procedure

1 Answers1