Description:

Is there a way to build a query based upon a working list? I.e., I have a list of 50 bird species and I would like to know which ones we are tracking - can I write a query that only looks at those 50 elements (that list)?

Solution:

Yes. This is easily and often done. In order to do this, you need to be familiar with the working list subject area of the data model. You will find a diagram in the System Tables portion of the diagram. The main table is WORKING_LIST. It contains the id, the name, who created it, when it was created, what type it is, etc. In other words, all the summary information about a working list. Each working list will have zero, one, or many rows associated with it in a working list data table. These are the WORKING_LIST_ELEMENT_DATA (for element wls), WORKING_LIST_EO_DATA (for EO wls), WORKING_LIST_SRC_FEAT_DATA (for source feature wls), etc. The only columns each of these data tables have are working_list_id and data_id. The working_list_id is a FK from WORKING_LIST. The data_id is a FK from whatever type of table is on the working list. For example, the data_ids in the WORKING_LIST_EO_DATA table all link back to EO.eo_id. You just have to join on data_id = eo.eo_id to get from the working list to the EO.

So, to give you an example, if you wanted to find all the S1 ranked elements that were on a working list named "My favorite birds", you'd have something like this:

SELECT EST.*
FROM element_subnational est,
working_list wl
working_list_element_data wled
WHERE wl.working_list_name = 'My favorite birds' and
wl.working_list_id = wled.working_list_id and
wled.data_id = est.element_subnational_id and
est.s_rank = 'S1';

This is a very good way to avoid extremely complex queries. You can break your query down into smaller pieces and create working lists of the results. Then you can do further searching within the working list itself.