Jay A. Kreibich wrote: > > You can't with just SQL. This is the whole issue with adjacency lists. > Most basic operations, like finding ancestor lists, counting tree depths, > finding a list of all children or descendants, etc., require some > kind of loop. > > I have posted about the "materialized path" approach to trees in SQL which I have used very successfully. See http://www.mail-archive.com/sqlite-users@sqlite.org/msg13225.html for the original post (also see http://www.mail-archive.com/sqlite-users@sqlite.org/msg19507.html for some additional ideas about formatting reports). This is a variation of the adjacency list technique that also stores a path through the tree to each node (hence the name "materialized path" as opposed to a virtual path stored as a sequence of links in the adjacency list). This path is maintained automatically by triggers.
Having the paths available turns many common tree queries into pattern matches against the path using like conditions on standard SQL queries. Unless your tees are very large I find this method to be a very good alternative to simple adjacency lists or nested sets. HTH Dennis Cote _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
