Re: self referencing table.

On 1/17/12 6:00 PM, Chris Travers wrote:
> On Tue, Jan 17, 2012 at 4:31 PM, David Salisbury<salisbury@globe.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.
>
> 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