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 have the same issue in ArcGIS Pro 2.6.8 in a file geodatabase and in an Oracle 18c 10.7.1 enterprise geodatabase. * I don't have this problem with definition queries in ArcGIS Pro 3.2.2, only with Select By Attributes. There is a separate issue in 2.6.8 with definition queries: Esri Case #03387420 - Definition query on join returns incorrect rows. 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?

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Select By Attributes on joined data — For rows that are 1:M, all rows in the join table get selected, despite selection criteria

Bud

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 have the same issue in ArcGIS Pro 2.6.8 in a file geodatabase and in an Oracle 18c 10.7.1 enterprise geodatabase.
I don't have this problem with definition queries in ArcGIS Pro 3.2.2, only with Select By Attributes. There is a separate issue in 2.6.8 with definition queries: Esri Case #03387420 - Definition query on join returns incorrect rows.

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?

Find more posts tagged with

Geodatabase

Accepted answers

GeeteshSingh07

@Bud , found this: https://support.esri.com/en-us/bug/the-select-by-attributes-tool-does-not-select-only-reco-bug-000154798

BUG-000154798: The 'Select By Attributes' tool does not select only records with specified field values added through a one-to-many join but instead selects all records with duplicate ObjectIDs.

All comments

GeeteshSingh07

@Bud, that's strange.

I tried same workflow on 3.1.0 and couldn't reproduce this issue...I also used 'Add Join' tool..

What if you use 'Join Field' gp tool, does the issue occur?

What if before join you run 'Calculate Field' tool on <Null> fields and run the query:

Then, perform a join and run 'Select By Attribute' tool?

GeeteshSingh07

@Bud I realized that I performed 1:1 join, my bad!

Yes, the issue does occur with 1:M join:

It's should be logged as a BUG.

GeeteshSingh07

@Bud , found this: https://support.esri.com/en-us/bug/the-select-by-attributes-tool-does-not-select-only-reco-bug-000154798

BUG-000154798: The 'Select By Attributes' tool does not select only records with specified field values added through a one-to-many join but instead selects all records with duplicate ObjectIDs.

Bud

Your testing in 3.1.0 and that bug are very helpful. Thanks! I’m investigating.

I noticed that bug was submitted in December 29, 2022. I’m surprised the issue hasn’t been fixed yet; I view it as high priority.

My users are starting to get the impression that ArcGIS Pro can’t be trusted when it comes to tabular analysis and are looking for alternative software solutions that can be trusted. Related issue: Add Join not keeping all target features and Esri Case #03387420 - Definition query on join returns incorrect rows.

If anyone from Esri sees this, can I suggest that this issue be made a priority? I imagine the last thing Esri wants is for users to not trust ArcGIS Pro when it comes to querying GIS data.

Bud

Since this issue is specific to 1:M joins, a possible workaround is to make the join 1:1 instead of 1:M:

Use the Make Query Table tool to hide the OBJECTID field in the join table. Join tables that don't have an OBJECTID field are automatically treated as 1:1 (one-to-first) joins.
https://community.esri.com/t5/arcgis-pro-ideas/choose-if-join-will-be-one-to-first-or-1-many/idc-p/1368759/highlight/true#M27968
Or, possibly join to a query layer or an unregistered database view, since I assume a join to an unregistered item will be one-to-first, similar to joining to an unregistered table: https://community.esri.com/t5/arcgis-pro-ideas/1-m-join-using-unregistered-tables-query-layers/idi-p/1473535
Alternatively, ArcGIS Pro 3.3.0 has an OOTB option to choose if the join will be 1:1 or 1:M. So in 3.3+, choose 1:1 when making the join. https://community.esri.com/t5/arcgis-pro-ideas/choose-if-join-will-be-one-to-first-or-1-many/idc-p/1407322/highlight/true#M29286

Or, to control what related record gets used in a 1:1 join, use this as the SQL expression in a Make Query Table (screenshot) :

table_b.objectid IN (SELECT objectid FROM table_b t2 WHERE t2.b_id = table_b.B_id ORDER BY b_text_field ASC FETCH FIRST ROW ONLY)

SQL expression source: Join — Control what related record gets used

The Make Query Table should include the OBJECTID field, since the SQL expression above references the OBJECTID.

Then, join to the Make Query Table.

The benefit of this approach is that it avoids issues with joining to a table that has a definition query, such as Add Join not keeping all target features and other bugs. The Make Query Table hides/embeds the SQL expression within the layer (it doesn't create a definition query), so that's how the usual "joining to a table with definition query" issues are avoided.

Also, as mentioned in a previous comment, definition queries don't have this problem. The problem is specific to selections. So in some cases, maybe a definition query could be used instead of a selection.

Other alternatives:

Do the join using SQL in a query layer or database view (the input table won't be editable, unlike Add Join). Ensure there is a truly unique ID field, possibly by using something like Oracle’s ROWNUM pseudo column, or the equivalent in your database.
Or, pare down the related table in a query layer/database view, so that joining to the query layer/database view is a 1:1 join. Editing the input table will be possible. But the join will be slow for medium to large datasets.
Edit: With that said, performance might have improved in 3.3.0:
"The geoprocessing framework uses workspace caching to improve performance when running scripts or repeated processes. This improvement applies particularly to remote data sources, such as enterprise geodatabases."
Or, export the data to the same enterprise geodatabase, or to a file geodatabase or mobile geodatabase. Do the selections there.
Or, for standalone tables, switch from joining from TABLE_A to TABLE_B, to joining from TABLE_B to TABLE_A. That might change the join from being 1:M to M:1 (1:1), which would eliminate the selection issue. But editing might be a problem, since only the input table can be edited in a join, not the join table.

JillScholz_esri

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.

Bud

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.

Bud

Disallow select by attributes on 1:M join with duplicates

Bud

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.

Bud

Add Join — Pseudo-ObjectID (row number) as unique ID field for 1:M join

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.