Title edited. Originally "1:M Join — Select By Attributes handling nulls incorrectly."
ArcGIS Pro 3.2.2; File Geodatabase:
I've joined TABLE_A to TABLE_B via common ASSET_ID text fields using Add Join. The join is one-to-many: 1 row in TABLE_A to 4 rows in TABLE_B.
I want to select from TABLE_B.ROAD_NAME (text) where ROAD_NAME is null. However, Select By Attributes doesn't seem to work correctly. It selects all four rows, regardless if ROAD_NAME is null or not.
Video:
Notes:
I don't understand how that issue has been around since ArcGIS Pro 2.6.8 (or earlier) and is also in 3.2.2. How could such a serious issue not have been fixed as of 3.2.2?
Solved! Go to Solution.
Hi @Bud,
Unfortunately, this is a known limitation.
As you suspected above, ObjectIDs are stored when we perform a selection. Situations where we end up with duplicate ObjectIDs can be a bit misleading. We haven’t duplicated the number of features in a layer, only their records within the table view. In this instance, both records from the right table are joined to the same feature (id=2) from the layer. Since there are multiple records joined to the same feature (with id=2), the selection of the one ObjectID results in all of those rows being highlighted in the attribute table window.
Thanks for taking the time to post this to our Community forum and also for providing several alternatives for others to potentially work around this issue.
For my notes:
The bug report was recently changed from “In Review” to “Known Limit”.
Known Limit: After review by the development team, it has been determined that this issue is related to a known limitation with the software that lies outside of Esri's control.
So, that means the issue cannot be fixed.
Make Query Table (Data Management)
You can provide a key field option and key fields list. This information defines how rows are uniquely identified and is used to add a dynamically generated Object ID field to the data. Without an Object ID field, selections will not be supported.
How to join using Make Query Table?
Limitation: The data in a Make Query Table can't be edited.