Re: self referencing table.

Hi,

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