On 1/17/12 6:00 PM, Chris Travers wrote:
On Tue, Jan 17, 2012 at 4:31 PM, 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.So basically you are just getting genus and species, why not just join the table against itself? It's not like you need recursion here. Something like: select g.latin_name as genus, s.latin_name as species from "Taxa" s join "Taxa" g ON s.parent_id = g.id WHERE s.taxonomic_rank = 'species' AND s.id = ? If you want the whole taxonomic ranking, you'd probably have to do a with recursive....... Best Wishes, Chris Travers
Well, that works fine if my s.id is pointing to a row that has a taxonomic_rank of 'species'. But that's not always the case. If there is no species for a plant's classification, the rank will be 'genus' for that s.id, so the query above would return nothing. Instead, for that case I'd like the query to return s.latin_name as genus, and null for species. I'm wondering if I'm missing something clever to do this, but I'm seeing this logic as row based iteration type stuff.. :( Cheers, -ds -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
