topic Re: 1:M Join — Select By Attributes handling nulls incorrectly in ArcGIS Pro Questions

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

Bud — Wed, 06 Mar 2024 20:37:21 GMT

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:

(view in My Videos)

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?

Re: 1:M Join — Select By Attributes handling nulls incorrectly

GeeteshSingh07 — Tue, 05 Mar 2024 04:52:32 GMT

@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?

Re: 1:M Join — Select By Attributes handling nulls incorrectly

GeeteshSingh07 — Tue, 05 Mar 2024 04:56:20 GMT

@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.

Re: 1:M Join — Select By Attributes handling nulls incorrectly

GeeteshSingh07 — Tue, 05 Mar 2024 12:32:25 GMT

@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.

Re: 1:M Join — Select By Attributes handling nulls incorrectly

Bud — Tue, 05 Mar 2024 16:59:37 GMT

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.

Re: 1:M Join — Select By Attributes handling nulls incorrectly

Bud — Fri, 08 Mar 2024 05:58:05 GMT

@GeeteshSingh07 As the bug suggests, the issue isn't specific to nulls. It applies to any value. The bug title described it fairly well.

The bug title states the problem is with the duplicate ObjectIDs, which seems accurate. ArcGIS Pro is very dependent on ObjectIDs, so best guess is: it's making the selection using a given ObjectID. Since there are duplicate ObjectIDs, it's selecting all rows that have that ObjectID, which is incorrect.

Here's an example of Select By Attributes working correctly on joined data, since for all rows, the join is 1:1:

Re: 1:M Join — Select By Attributes handling nulls incorrectly

GeeteshSingh07 — Fri, 08 Mar 2024 06:23:19 GMT

@Bud , I agree..and should be fixed!

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

Bud — Wed, 01 May 2024 03:30:28 GMT

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

Bud — Thu, 16 May 2024 14:47:52 GMT

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.

Re: 1:M Join — Select By Attributes handling nulls incorrectly

Bud — Tue, 07 May 2024 19:25:53 GMT

It turns out the issue doesn't only apply to Select By Attributes. It also applies to manual selections.

With a 1:m join, if I manually select one of the rows that is 1:M, then all rows in the 1:M group will get selected, which is incorrect.

Video (using different sample data):

(view in My Videos)

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

JillScholz_esri — Wed, 15 May 2024 16:28:27 GMT

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.

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

Bud — Fri, 17 May 2024 21:19:44 GMT

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.

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

Bud — Wed, 28 Aug 2024 04:29:16 GMT

Disallow select by attributes on 1:M join with duplicates

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

Bud — Thu, 05 Sep 2024 13:51:53 GMT

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.

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

Bud — Thu, 05 Sep 2024 15:04:13 GMT

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