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(at)globe(dot)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