Skip to content
Advertisement

SQL (?) How to parse parent category from breadcrumb to update fields with parentIDs?

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

SQL Fiddle

I dont have enough reputation to add a comment,so I push a new answer.Sorry for that.

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement