yb_cte_example.sql

gistfile1.txt

#
CREATE DATABASE IF NOT EXISTS `test_cte`;
USE `test_cte` ;
#
CREATE  TABLE IF NOT EXISTS `test_cte`.`trees` (
  `id` INT NOT NULL ,
  `parent_id` INT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;
#
INSERT INTO `test_cte` VALUES (1,3), (2,5), (3,NULL), (4,10), (5,NULL), (6,1), (7,5), (8,10), (9,10), (10,3);

5
├── 2
└── 7

3
├── 10
│   ├── 4
│   ├── 9
│   └── 8
└── 1
    └── 6

# 
with recursive ancestors as (
  select * 
  from trees
  where id = 4
  union
  select trees.*
  from trees, ancestors AS a
  where
    trees.id = a.parent_id
)
select * from ancestors;