ArcGIS cannot perform a one-to-many join between a feature class and a table located in a remote database. However, with the right definition query, it is possible to reduce the set of records from a one-to-many a one-to-one.

 

Scenario: a feature class of point locations with an associated table of monthly observations, one per location, located in a remote DB. Left unfiltered, the join would be one-to-many and only the first matching record would be returned for each point. But a definition query targeting one particular month “should” or “could” result in a one-to-one join. We have found that when Oracle is the remote DB, this works pretty reliably (we think). When SQL Server is the remote DB, the results are unpredictable.

 

This leads me to think that the definition query may be applied before or after the join, depending on the DBMS – but I have not found any documentation detailing the expected behavior.

 

Question: can anyone point me to documentation detailing the order of evaluation when both a join and a definition query are present?

 

Thanks – Andy