Обсуждение: self referencing table.
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,
-ds
On Jan 17, 2012, at 19:31, 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. > > 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.
Hi, On 18 January 2012 11:31, 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 You should check Joe Celko's book: Trees and hierarchies in SQL for smarties It has many good ideas about storing and accessing tree-like structures in relational databases. (just google for chapter names :)). I have this link in my bookmarks but it doesn't work anymore: http://dev.mysql.com/tech-resources/articles/hierarchical-data.html -- quite good article about nested sets For example in "nested sets" model finding the path is simple query like this: SELECT taxonomic_rank FROM Taxa WHERE lft < $left AND rgt > $right ORDER BY lft ASC; where $left, $right are lft and rgt values from required taxa.id -- Ondrej Ivanic (ondrej.ivanic@gmail.com)
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
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@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 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
On 19/01/2012 12:57 AM, David Salisbury wrote: > 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 ). > Usually recursive common table expressions (`WITH RECURSIVE') are used to handle queries on self-referential tables. Have a look in the documentation for more information. -- Craig Ringer