I have a bit of a tricky problem hoping someone can help me with.
I have a category structure that includes:
- id
- category path
- category name
Example:
1 Root/Arts Arts 2 Root/Arts/Animation Animation 3 Root/Arts/Animation/Anime Anime 4 Root/Arts/Animation/Anime/Characters Characters 5 Root/Arts/Animation/Anime/Clubs_and_Organizations Clubs_and_Organizations 6 Root/Arts/Animation/Anime/Collectibles Collectibles 7 Root/Arts/Animation/Anime/Collectibles/Cels Cels 8 Root/Arts/Animation/Anime/Collectibles/Models_and_Figures Models_and_Figures 9 Root/Arts/Animation/Anime/Collectibles/Models_and_Figures/Action_Figures Action_Figures 10 Root/Arts/Animation/Anime/Collectibles/Models_and_Figures/Action_Figures/Gundam Gundam 11 Root/Arts/Animation/Anime/Collectibles/Models_and_Figures/Action_Figures/Zoids Zoids 12 Root/Arts/Animation/Anime/Collectibles/Models_and_Figures/Models Models 13 Root/Arts/Animation/Anime/Collectibles/Models_and_Figures/Models/Gundam Gundam 14 Root/Arts/Animation/Anime/Collectibles/Shitajiki Shitajiki
What I need is to identify the parent category for each row so the table would look something like this:
- id
- name
- path
- parent_id
The data is in SQL but I have text files as well and open to working on the command line to accomplish this. Maybe its a better tool for the job?
Here is an SQL fiddle using the query provided by below and the sample data above:
http://sqlfiddle.com/#!9/e060ec/1
Thanks in advance for any guidance.
Advertisement
Answer
I have correct the sql.
SELECT c.id, c.name, c.path, p.id AS parent_id FROM cat_test p RIGHT JOIN ( SELECT id, name,path, LEFT(path,LENGTH(path) - LENGTH(name)-1) AS parent FROM cat_test) c ON c.parent=p.path
I dont have enough reputation to add a comment,so I push a new answer.Sorry for that.