Re: self referencing table.

On 1/18/12 9:46 AM, David Salisbury wrote:
>
>
> 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

Think I'll answer myself on this. I'll join in whatever rows I get from
the self referential query above to the base table, and include the rank column,
and then figure out some sort of post processing on the resultant view ( I hope ).

-ds