CODEWITHSUNDEEP
sqlhsqldbhierarchical-datarecursive-query
Reza Tashtboland
Reza Tashtboland

Reputation: 13

SQL query to select parent and child names in one row

i have table in hsqldb and values like this

enter image description here

How do I write a query that displays like this

enter image description here

Upvotes: 0

Views: 1746

Answers (3)

fredt
fredt

Reputation: 24372

An alternative, simpler query based on the accepted answer. We start only with the roots (rows with null parents) and each time the union is performed, only a few rows are added. As the answer indicates, this kind of query will not select child rows with invalid parent id:

with recursive n (root_id, id, title, parent) as (
  select id as root_id, id, name, parent from t where parent is null
 union
  select n.root_id, t.id, n.title || '/' || t.name, t.parent
  from t
  join n on n.id = t.parent
)
select * from n

For this kind of table, it is essential to add the referential constraints that are implicit in the design. This will speed up queries when the table gets larger and will also avoid broken parent ids.

alter table t add constraint pk primary key(id)
alter table t add constraint fk foreign key (parent) references t(id)

Upvotes: 0

GMB
GMB

Reputation: 222672

You can use a recursive query:

with recursive cte (id, name, parent, path, lvl) as
    select id, name, parent, name, 0 from mytable
    union all
    select c.id, c.name, t.parent, concat(t.name, '/', c.path), c.lvl + 1
    from cte c
    inner join mytable t on t.id = c.parent_id
)
select *
from cte c
where lvl = (select max(c1.lvl) from cte c1 where c1.id = c.id)

The query selects the whole table, then climbs the hierarchy tree of each row. The upside of this approach is that it would properly handle a "broken" tree (like a child with an invalid parent for example).

Upvotes: 1

The Impaler
The Impaler

Reputation: 48865

Here you go:

with recursive
n (root_id, id, title, parent) as (
  select id as root_id, id, name, parent from t
 union
  select n.root_id, t.id, t.name || '/' || n.title, t.parent
  from n
  join t on t.id = n.parent
)
select title as name from n where parent is null order by root_id

Result:

NAME      
----------
A         
A/A1      
B         
B/B1      
C         
C/C1      
A/A1/A2   
A/A1/A3   
A/A1/A3/A4
B/B1/B3

For reference this is the data script I used to test:

create table t (id int, name varchar(10), parent int);

insert into t (id, name, parent) values
  (0, 'A', null),
  (1, 'A1', 0),
  (2, 'B', null),
  (3, 'B1', 2),
  (4, 'C', null),
  (5, 'C1', 4),
  (6, 'A2', 1),
  (7, 'A3', 1),
  (8, 'A4', 7),
  (9, 'B3', 3);

Upvotes: 1

Related Questions