On Mon, May 17, 2010 at 09:40:32PM +0800, zeal scratched on the wall: > dear Sqlite group, > i wonder is there RECURSIVE select function in sqlite? the background > for the question are: > create table objects (id INTEGER PRIMARY KEY AUTOINCREMENT, name text unique) > create table tree(id int, child_id int, PRIMARY KEY(id, child_id)) > i want to draw the whole tree, is there good solution for the function? > thanks.
No, recursive selects are not supported. If you use an adjacency model (like your example) you need to have your application loop over the tree (for arbitrary sized trees; if you know the depth of the tree you can use a series of joins). If you use a nested set model, you can do more with single queries, but the insert/update/delete costs are much higher. See any good SQL book and/or web tutorials for more info. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
