On Jan 17, 2012, at 19:31, David Salisbury <salisbury@xxxxxxxxx> 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
