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-attri...
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_aQuestion:
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?
Accepted Solutions
