One-to-first join — Intuitive way to filter related table prior to joining

[Bud]

ArcGIS Pro 3.3.5; File Geodatabase

I have a roads FC and an events standalone table. The relationship of the underlying data is one-to-many (no relationship class). One road to many events via a common ROAD_ID field.

I want to make a one-to-first join from roads to events. But I only want to join to events where the EVENT_STATUS='ACTIVE'.

Creating an EVENT_STATUS='ACTIVE' definition query on events wouldn't be suitable because once the join is created, the filter will be applied by Pro after the one-to-first join is performed, not before. I need the filter to be applied before the join is performed, so that only ACTIVE events are joined.

I don't want to make a copy of the filtered data; that's messy and causes confusion. And I don't want to use Make Query Table to pre-filter the data; the resulting filter isn't visible anywhere in Pro, so it isn't modifiable and confuses users (also, joining to query tables is slow). I would prefer not to create a database view each time I do tabular analysis like this in Pro; that would be cumbersome.

I want to be able to edit the roads features/fields in the attribute table, as well as see fields from the events table, so a join is the only suitable option.

It would help to have a more intuitive way to filter the related table prior to joining for ad-hoc, dynamic analysis.

[MarceloMarques]

- Move the data from the File Geodatabase to an Enterprise Geodatabase (Oracle, SQL Server, PostgreSQL).

- Create a Database View using a SQL Statement to return only the first matching row.

https://stackoverflow.com/questions/35255271/sql-how-to-limit-a-join-on-the-first-found-row

https://forums.oracle.com/ords/apexds/post/returning-the-first-record-of-an-inner-join-3311

https://dba.stackexchange.com/questions/6368/how-to-select-the-first-row-of-each-group

- File Geodatabases do not support advanced SQL statements, hence it is necessary to load the data into an Enterprise Geodatabase to take advantage of the RDBMS advanced SQL features.

[Bud]

@MarceloMarques Yes, I've tried this in our Oracle EGDB. The problem is the attribute table is unreliable when joining to a database view or query layer. I think you and I have discussed this in other posts. The attribute table table becomes slow and the joined fields display nulls sometimes. Unfortunately, I have lost faith in Pro's ability to join to SQL queries. Submitting Esri Support cases has not been fruitful.

[MarceloMarques]

@Bud - change "null" values for a value like "zero" in the view SQL statement. If the view is still slow to query, then perform the tuning by analyzing the execution plan when you run a select statement on the view, by seeing the execution plan in detail then you can identify what causes the view to be slow. If the view is slow to query because the dataset is very large, then use a "materialized view", you will need to schedule the "materialized view" refresh according to your needs to refresh the data, you can accomplish this automatically via an oracle dbms_scheduler job that calls the SQL commands to refresh the "materialized view", oracle documentation has examples.

[Bud]

The way one-to-first joins work is: Pro grabs the related record with the lowest OBJECTID.

So, if a road has a couple of related events, but the event with the lowest OBJECTID is an EVENT_STATUS='INACTIVE' event, then that event will be joined, not the other EVENT_STATUS='ACTIVE' event with the larger OBJECTID.

If I had an EVENT_STATUS='ACTIVE' definition query on events prior to creating the join, Pro will automatically apply that definition query to the joined roads table when I create the join. In the example above, the definition query would omit the road entirely because the joined event is INACTIVE. So, that’s what I mean when I say the definition query is performed after the join is performed. I’m looking for a way to filter the table prior to the join being performed so that only ACTIVE events are joined.

I don’t want to do a one-to-many join because I want a unique list of roads. If there were a road with multiple ACTIVE events, then that would result in duplicate roads in the attribute table if the join were one-to-many.

Related:

• Add Join — Indicate that a definition query on join table will effectively result in an inner join
• Add Join — Create independent definition query on join table (pre-filter)
• One-to-first join — Control what related record gets used