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

411
6
Jump to solution
03-04-2024 07:27 PM
Labels (1)
Bud
by
Notable Contributor

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:

  1. 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.
  2. 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?

1 Solution

Accepted Solutions
GeeteshSingh07
Occasional Contributor II

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

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.

View solution in original post

6 Replies
GeeteshSingh07
Occasional Contributor II

@Bud, that's strange.

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

GeeteshSingh07_0-1709613941857.png

GeeteshSingh07_1-1709613992353.png

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:

GeeteshSingh07_2-1709614299549.png

 

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

 

0 Kudos
GeeteshSingh07
Occasional Contributor II

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

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

GeeteshSingh07_3-1709614521643.png

 

It's should be logged as a BUG.

 

 

GeeteshSingh07
Occasional Contributor II

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

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
by
Notable Contributor

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
by
Notable Contributor

@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:
 
Bud_0-1709755573537.png
GeeteshSingh07
Occasional Contributor II

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