multiple-column-tree

dump.sql

DROP TABLE IF EXISTS `xtree`;

CREATE TABLE IF NOT EXISTS `xtree` (
gid BIGINT,
line BIGINT, 
id VARCHAR(3),
comments VARCHAR(10),
level BIGINT );

insert into `xtree` values ( 1, 1,  'A',   'A', 1);
insert into `xtree` values ( 1, 2,  'B',   'B', 2);
insert into `xtree` values ( 1, 3,  'E',   'E', 3);
insert into `xtree` values ( 1, 4,  'F',   'F', 3);
insert into `xtree` values ( 1, 5,  'C',   'C', 2);
insert into `xtree` values ( 1, 6,  'G',   'G', 3);
insert into `xtree` values ( 1, 7,  'D',   'D', 2);
insert into `xtree` values ( 1, 8,  'H',   'H', 3);
insert into `xtree` values ( 1, 9,  'J',   'J', 4);
insert into `xtree` values ( 1, 10, 'K',   'K', 4);
insert into `xtree` values ( 1, 11, 'L',   'L', 4);
insert into `xtree` values ( 1, 12, 'I',   'I', 3);
insert into `xtree` values ( 1, 13, 'END', 'END', 0);

insert into `xtree` values ( 2, 1,  'A',   'A', 1);
insert into `xtree` values ( 2, 2,  'B',   'B', 2);
insert into `xtree` values ( 2, 3,  'E',   'E', 3);
insert into `xtree` values ( 2, 4,  'F',   'F', 3);
insert into `xtree` values ( 2, 5,  'C',   'C', 2);
insert into `xtree` values ( 2, 6,  'G',   'G', 3);
insert into `xtree` values ( 2, 7,  'D',   'D', 2);
insert into `xtree` values ( 2, 8,  'H',   'H', 3);
insert into `xtree` values ( 2, 9,  'J',   'J', 4);
insert into `xtree` values ( 2, 10, 'K',   'K', 4);
insert into `xtree` values ( 2, 11, 'L',   'L', 4);
insert into `xtree` values ( 2, 12, 'I',   'I', 3);
insert into `xtree` values ( 2, 13, 'END', 'END', 0);

query.sql

-- get node and all sub nodes  

select * from xtree 
where gid = 1 and line>=8 
and line < (
  select min(line) from xtree 
  where gid = 1 and line>8 and level<=3
) 

-- get only sub nodes  
select * from xtree 
where gid = 1 and line>=8 and level=(3+1) 
and line < (
  select min(line) from xtree 
  where gid = 1 and line>8 and level<=3
) 

-- get node and all parent nodes and reverse level

select x.gid, x.id, x.comments, x.line, x.level,
abs(x.level - p.blv ) as reverse
from (
  select max(t.line) as line, t.level, t.gid, u.level as blv
  from (
    select * from `xtree` 
    where gid = 1 and id = 'I'
  ) u inner join `xtree` t 
  on u.gid = t.gid 
  and u.line >= t.line
  and u.level >= t.level
  group by t.level, t.gid
) p inner join `xtree` x
on p.gid = x.gid
and p.level = x.level
and p.line = x.line

ref.md

https://github.com/drinkjava2/Multiple-Columns-Tree

https://drinkjava2.iteye.com/blog/2353983