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?