Feature #342

hierarchical SQL queries

Added by Miroslav Fidler over 9 years ago. Updated over 7 years ago.

Status:ApprovedStart date:10/23/2012
Priority:NormalDue date:
Assignee:-% Done:

0%

Category:-Spent time:-
Target version:-

Description

http://www.postgresql.org/docs/8.4/static/queries-with.html

WITH RECURSIVE r(parent_id, name, id, t) AS
(
/*Prvni iterace*/
(
(select parent_id, name, id, 0
from treenode
where nodeid = '170401000'
)
union
(select d.parent_id, d.name, d.id, 10
from treenode o inner join treenode d
on d.doubled_id = o.id
where o.nodeid = '170401000'
)
)
UNION ALL
/*Iterace dokud neco pribyva*/
select p.parent_id, p.name, p.id, r.t+1
from treenode p inner join r
on r.parent_id = p.id
where p.id > 0 and r.id not in
(
select treenode_id from treenode_attr
where entity = -106
)
)
select * from r order by r.t;

History

#1 Updated by Miroslav Fidler about 9 years ago

  • Subject changed from hierarchicke dotazy postgresql to hierarchical SQL queries

#2 Updated by Miroslav Fidler over 7 years ago

  • Status changed from New to Approved

Also available in: Atom PDF