From: | David Salisbury <salisbury(at)globe(dot)gov> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | self referencing table. |
Date: | 2012-01-18 00:31:10 |
Message-ID: | 4F1612CE.3050702@globe.gov |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | David Johnston | 2012-01-18 01:01:07 | Re: self referencing table. |
Previous Message | Tim Uckun | 2012-01-17 22:34:04 | Re: HA options |