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,
-ds