Re: self referencing table.

Hi,

On 18 January 2012 11:31, David Salisbury <salisbury(at)globe(dot)gov> 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(dot)ivanic(at)gmail(dot)com)