Get hierarchy from "parent linked" database

Question

I have an existing (old) sqlite database structure that looks like this:

╔══════════╦═══════════╦══════════╗
║ id_group ║ id_parent ║ sequence ║
╠══════════╬═══════════╬══════════╣
║        1 ║         0 ║        0 ║
║       10 ║         1 ║       70 ║
║      117 ║        10 ║       30 ║
║      124 ║       117 ║       40 ║
║      174 ║      3998 ║       60 ║
╚══════════╩═══════════╩══════════╝

Where id_parent points to another id_group to create hierarchy.

How can I create a query that will give me the whole hierarchy in one go? Of course, I could just go through all the id_parents and write down each level of the hierarchy, but that seems unnecessary and also tedious.

PS: If it's not possible with SQL alone, I can also utilize PHP, C#, Python and whathaveyou.

score 2 · Accepted Answer · answered Apr 15 '14 at 08:56

If you are using the a current version of SQLite you can use a (ANSI standard compliant) recursive query:

with recursive group_tree as (
  select id_group, 
         id_parent, 
         sequence
  from groups
  where id_parent = 0 -- marks the start of your tree
  union all
  select c.id_group, 
         c.id_parent,
         c.sequence
  from groups p
    join group_tree c on p.id_group = c.id_parent
) 
select *
from group_tree;

If you want to start anywhere else in the hierarchy, just replace the where id_parent = 0 with e.g. where id_group = 10 to get all children of that group.

More details in the manual: https://www.sqlite.org/lang_with.html

Get hierarchy from "parent linked" database

1 Answers1