Join with definition query excludes row unexpectedly

Bud — Thu, 19 Sep 2024 18:15:56 GMT

ArcGIS Pro 3.3.1; mobile geodatabase or file geodatabase

Mobile Geodatabase:

I have TABLE_A:

And TABLE_B:

I've hidden the OBJECTID fields from both attribute tables to remove visual clutter.

I want to join from TABLE_A to TABLE_B using the common ID fields. I want the join relationship to be one-to-first, not one-to-many. When it comes to "what related record to use from the join table" in the one-to-first join, I want to use the rows from TABLE_B where BINARY = 1.

Background: https://community.esri.com/t5/data-management-questions/1-m-join-to-pull-feature-with-specific-attribute/m-p/1524843/highlight/true#M45338

I apply a definition query to TABLE_B: WHERE BINARY = 1.

Then I create the join:

But, due to the definition query being applied to the join, the row from TABLE_A where ID_A=3 has been excluded. That is expected since there is no matching row in TABLE_B where the ID is 3.

So, I will modify the definition query on the joined table to include ...OR ID_B IS NULL.

However, the new definition query excludes the row where ID_A=1. That's not expected.

Definition Query: main.TABLE_B.BINARY = 1 OR main.TABLE_B.ID_B IS null

I expected the attribute table of the join to look like this (using DBeaver 23.3.2 SQL client):

select
    a.id_a,
    b.id_b,
    b.binary
from
    table_a a
left join
    table_b b   
    on a.id_a = b.id_b
where
    binary=1 or id_b is null
order by
    id_a

Question:

Why is the one-to-first join with the definition query main.TABLE_B.BINARY = 1 OR main.TABLE_B.ID_B IS null excluding the row where ID_A=1?

Re: Join with definition query excludes row unexpectedly

Bud — Thu, 19 Sep 2024 18:14:21 GMT

It appears to be a bug.

Esri Case #03704340 - ArcGIS Pro 3.3.1 - Join with definition query excludes row unexpectedly

BUG-000170869: Adding a definition query excludes a row of data when using Add Join geoprocessing tool in ArcGIS Pro

topic Re: Join with definition query excludes row unexpectedly in ArcGIS Pro Questions

Join with definition query excludes row unexpectedly

Re: Join with definition query excludes row unexpectedly