Re: self referencing table.

On Jan 17, 2012, at 19: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
>
> phylum
> family
> order
> genus
> species
>
> So at any row in the table I can get all the parent
> information be traversing upward using the parent id.
>
> However I'm interested in only getting just genus and species
> when I'm given a taxa.id value. It would be a nice simple
> self join if the taxa.id I was given was always to a
> row with rank of 'species'. Problem is, grasses don't
> have species, so sometimes my id is pointing to a genus
> row instead ( the id will be to lowest rank ), so the
> parent is of no use.
>
> I'm thinking that I'm going to have to resort to a plpgsql
> function to solve this, but I'm hoping for a straight sql
> query that can return ...
>
> genus, species ( if it's a grass id the species value will be null )
>
> ... for any given taxa.id without iterating in a function.
>
> Actually, I was hoping to join this taxa table with it's
> referencing table and pull a view with the genus/species
> added in.
>
> possible? Thanks for any ideas,
>
>

8.4 or better you should be able to use WITH RECURSIVE.

David J.