DESCRIPTION

I am trying to develop a query to aid in updating our rankings. It needs to include the eo data, the last obs date, and the first obs date. I thought it would be good to include the info. from the VISIT table, as well. However, I do not know on which field to join.

Let me give an example.

In Tracker, I search for an EO and find that that the eo_id is =6021.

I then click on the Source tab and see that the Source_ID is 32561. Based on your previous response, I can use this source id (source_feature_id) to join to the VISIT table. No problems there. HOWEVER, how do I get this to join back to the EO? I have done a simple quer y on eo_id=6021 and do not see anything in the EO table that I could use to join to either the VISIT or source_feature.

I know something HAS to exist because in Tracker, everything is neatly organized.

 
RESOLUTION

The VISIT table is linked to the SOURCE_FEATURE table via the source_feature id. You get to it like this:
WHERE source_feature.source_feature_id = VISIT.source_feature_id
etc. etc.

The missing link is the EO_SOURCE_FEATURE table. This is the table that links a source feature with its EO and an EO with its one or many source features. It represents the relationship between one source feature and one EO. This is the table that you must join in order to hop from source feature to EO. So if you wanted to hop from VISIT to EO, you have something like this:
 

WHERE VISIT.source_feature_id = source_feature.source_feature_id AND
source_feature.source_feature_id = eo_source_feature.source_feature_id AND
eo_source_feature.eo_id = eo.eo_id

This has been simplified in Biotics 5 due to the addition of the SOURCE_FEATURE_ID and EO_ID columns to the EO_SOURCE_FEATURE table.  Previously, the table only contained the SHAPE_IDs of the two features (to minimize coding changes when Biotics 3.1 was redeveloped into Biotics 4).