The view SCI_NAME_USAGE_VW makes this very easy. The fields are:
| SN_ID |
the scientific_name_id |
| SCI_NAME |
the scientific_name for that record |
| TABLE_USED |
the table in which the sn_id was used |
| RECORD_ID |
the primary key for the record in which the sn_id is used |
| ASSOC_ELEMENT_ID |
the id of the associated element record (so if the table_used is SYNONYM_GLOBAL, this will be the element_global_id of the record that uses the synonym; if the table_used is ELEMENT_SUBNATIONAL, this will be the element_subnational_id of the record that uses the scientific name - and in this case will match the record_id). |
| USED_AS |
the name of the column where the sn_id was found. Most of the time this will be scientific_name_id, but there are those that have different names, like gname_id and sname_id, etc. |
Here are some examples of how to use it.
1. If you just want to know where a particular scientific name is used, it's this simple; just enter the ID for the scientific_name record you're interested in for "sn_id":
SELECT * FROM sci_name_usage_vw WHERE sn_id = 104038
2. If you want 1 row per scientific name, you can use delimlist to return some of the information in delimited lists, e.g.:
select sn.scientific_name_id, sn.scientific_name, sn.author_name, dn.name_category_desc
, delimlist('select used_as from sci_name_usage_vw where sn_id=' || sn.scientific_name_id, '/') usage
, delimlist('select eg.elcode_bcd from sci_name_usage_vw sv, element_global eg
where sv.assoc_element_id = eg.element_global_id and sn_id=' || sn.scientific_name_id ||
' union select eg.elcode_bcd from sci_name_usage_vw sv, element_national en, element_global eg
where sv.assoc_element_id = en.element_national_id and en.element_global_id = eg.element_global_id
and sn_id=' || sn.scientific_name_id ||
' union select eg.elcode_bcd from sci_name_usage_vw sv, element_subnational es, element_national en, element_global eg
where sv.assoc_element_id = es.element_subnational_id and es.element_national_id = en.element_national_id and en.element_global_id = eg.element_global_id
and sn_id=' || sn.scientific_name_id, '/') elcodes
from scientific_name sn, d_name_category dn
where sn.d_name_category_id = dn.d_name_category_id
and sn.scientific_name_id in(186216,186218,186226)
3. Here's a way to get a list of each scientific_name_id, each table that it's found in, and how many occurrences there are in that table of that id:
select sn_id, table_used, count(distinct assoc_element_id)
from sci_name_usage_vw
group by sn_id, table_used