Hi, On 18 January 2012 11:31, David Salisbury <salisbury@xxxxxxxxx> wrote: > > I've got a table: > > Taxa > Column | Type > ----------------+----------------------------- > id | integer | > parent_id | integer | > taxonomic_rank | character varying(32) | > latin_name | character varying(32) > > It's basically a self referential table, with > values in the taxonomic_rank like You should check Joe Celko's book: Trees and hierarchies in SQL for smarties It has many good ideas about storing and accessing tree-like structures in relational databases. (just google for chapter names :)). I have this link in my bookmarks but it doesn't work anymore: http://dev.mysql.com/tech-resources/articles/hierarchical-data.html -- quite good article about nested sets For example in "nested sets" model finding the path is simple query like this: SELECT taxonomic_rank FROM Taxa WHERE lft < $left AND rgt > $right ORDER BY lft ASC; where $left, $right are lft and rgt values from required taxa.id -- Ondrej Ivanic (ondrej.ivanic@xxxxxxxxx) -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
