Re:SQLite now allows multiple recursive SELECT statements in...
by sradman on Wednesday October 21, 2020 @07:32AM
(#24846738)
Another good writeup is included in the SQL Anywhere documentation [1] which has a good example of a parts explosion problem [2]. I don't know if the syntax works in SQLite but it might be a good exercise to get it to work.
[1] http://dcx.sap.com/1200/en/dbusage/ug-commontblexpr.html
[2] http://dcx.sap.com/1200/en/dbusage/parts-explosion-cte-sqlug...
The Fossil discussion involves the SCM's representation of a File System, probably the most common hierarchical data structure we regularly encounter. Hierarchical data structures are notoriously hard in SQL. Recursive CTEs are not easy but they are useful and efficient for hierarchical data.
[1] https://www.sqlite.org/draft/lang_with.html#hierarchical_que...