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