3

I have two tables, categories and products. Categories table is a nested set model. Products table has a serial_number field that is unique. Their schema is like this :

Categories :

+----+-----------+-----+-----+-------+-------------+
| id | parent_id | lft | rgt | depth |    title    |
+----+-----------+-----+-----+-------+-------------+
|  1 | Null      |   2 |   9 |     0 | Cloth       |
|  2 | 1         |   3 |   6 |     1 | Men's       |
|  3 | 2         |   4 |   5 |     2 | Suits       |
|  4 | 1         |   7 |   8 |     1 | Women's     |
|  5 | Null      |  10 |  13 |     0 | Electronics |
|  6 | 5         |  11 |  12 |     1 | TVs         |
+----+-----------+-----+-----+-------+-------------+

Products :

+-------------+---------------+
| category_id | serial_number |
+-------------+---------------+
|           3 |    5461354631 |
|           3 |    4521516545 |
|           4 |    8513453217 |
|           6 |    1235624165 |
+-------------+---------------+

What I want is to create a view to show all serial_numbers with their category path :

+---------------+-------------------+
| serial_number |       path        |
+---------------+-------------------+
|    5461354631 | Cloth/Men's/Suits |
|    4521516545 | Cloth/Men's/Suits |
|    8513453217 | Cloth/Women's     |
|    1235624165 | Electronics/TVs   |
+---------------+-------------------+

What is the best query to generate this view?

Amir
  • 413
  • 4
  • 13
  • 1
    Pretty sure this should be done in a series of steps: step 1: select all records that have a left and right which can contain the leaf node. step 2: order by left. Step 3: GROUP_CONCAT using a forward slash as a SEPARATOR. Then finally select the serial number along side it. Once this SELECT works, can convert it to a view. I'll leave the work for someone else to do. – Ultimater Aug 20 '18 at 04:55
  • How deep can the tree be? – Nick Aug 20 '18 at 06:37
  • @Nick there is no limit – Amir Aug 20 '18 at 07:58
  • That makes it pretty difficult without a stored procedure as you have to traverse the tree backwards without knowing how deep you are. – Nick Aug 20 '18 at 08:22
  • @Nick I know this an old question and now I even don't need the answer, but I'm curious what could be that stored procedure you talked about. – Amir Oct 18 '18 at 08:39
  • 1
    @AmirhosseinDZ take a look at [this question](https://dba.stackexchange.com/questions/94876/mysql-how-to-write-a-recursive-function-to-show-menu-tree) for a generic example – Nick Oct 18 '18 at 09:28

0 Answers0