Honor Definition Query When Joining Feature Classes

11-13-2015 07:52 AM
Status: Closed
Labels (1)
New Contributor II

Currently, if a feature class has a definition query (feature class A), and is joined to another feature class (feature class B), only the matching records are displayed in the attribute table for the target feature class after the join (feature class B).  This behavior happens even if the "keep all records" option is selected when the join is created.  This is described as "expected behavior" in knowledge base article 17725, however, it makes tracking holes in the data much harder without extra steps.  It would be nice if the definition query on a feature class was inserted in the join logic to return all records in the original feature class (feature class B).  The current work around is to write a query in MS Access (that replaces the definition query in the feature class), then use an ODBC connection to make the resulting table from the Access query available in ArcMap.



I recently had a scenario where i was joining a non-spatial table to a spatial feature class. The feature class layer had a definition query on and had 212 rows, the table had 290 rows. 

Joining the feature class to the table i.e. the table being the 'target' and the feature class being the 'join table' (ESRI's words not mine) and ticking keep all features (or whatever the words are) resulted in only 212 rows, not 290 because apparently the definition query on the feature class overrides the rows in the table. 

To me (and to my ESRI support case officer), and to the rest of my team, this seems counter-intuitive.

So my idea is either

  1. flag the fact there is a definition query on the feature class and it will only return that many rows or
  2. make it the same functionality as when there is no definition query.

This was a terrible feature to transfer to ArcGIS Pro - 5 years on from this original idea and it's exactly the same. A definition query should not override basic functionality. arcgis-pro-2.6 definition-query #definition query join

Status changed to: Closed

This idea has been closed as a duplicate of the Data Management Idea linked below. Don't worry, you can still show your support by commenting and adding kudos to the open idea. Thanks for your contributions!

Link to open Data Management Idea