Firebird SQL best practices Review of some SQL features available and that people often forget about Author: Philippe Makowski IBPhoenix Email: [email protected]. A hierarchical query is a type of SQL query that handles hierarchical model data. They are special cases of more general recursive fixpoint queries, which compute transitive closures. In standard SQL:1999 hierarchical queries are implemented by way of recursive common table expressions (CTEs).
Crossposted byPostgreSQL4 months ago
•
Posted by4 months ago
It seems there's a simple solution to this, but I really can't imagine it. The following table that can be represented by this graph https://imgur.com/0XUKWiG is a model for a larger table.
The table and recursive query here: https://www.db-fiddle.com/f/ihSqVgfRoPPNd774jB2WhU/4 from /u/boy_named_su returns 31 rows which I can left join to get other information on the nodes.
The thing is, I sometimes know the source of the root nodes (in this example nodes 1,2, and 3), but the query doesn't work properly if I remove the 'where sid is null' part--it returns 86 rows instead.
Other than the solution of going into the data and creating a node for the true root or removing all sid from the root nodes, is there a query that can return 31 rows like the original, but have nodes 1,2, and 3 with 3 different sids, like below, and still create a path like 100|1|7|13 for node 13.
values
('1','100'),
('2','101'),
('3',103')
instead of
('1',NULL),
('2',NULL),
('3',NULL)
Thank You
6 comments
Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |