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

[Bud]

Regarding 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. (Status:  Known Limit)

• Select By Attributes on joined data — For rows that are 1:M, all rows in the join table get selected...
• Is ArcGIS Pro the right tool for tabular/join-based geodatabase analysis?

Could the various issues with 1:M (one-to-many) joins in Pro be solved by having a pseudo-ObjectID (row number) field as a unique ID?

For example, Oracle's ROWNUM pseudo-column works fine for query layers as the unique ID. Could a similar row-number-based mechanism work for Add Join in the attribute table, too?

select
    cast(rownum as int) as pseudo_objectid,
    lc.*,
    pf.*
from
    lc_events lc --one
left join
    lc_events_project_finances pf --many
    on lc.event_id = pf.event_id

Edit: I think this approach would work for M:M (many-to-many) joins too.

Related: Disallow 1:M join layers as GP inputs instead of producing unexpected results

[JonathanNeal]

@Bud Yes, internally selections work off of objectId's, stored in the layer.  When there is a 1:many join, selections will select duplicates.  For the Add Join tool, we would require additional development by the Geodatabase team. 
The team does have an issue for this:
 Title: "Relquerytable to support a unique generated field"
Note1: this is a tough task when implementing it across all data sources, so it will require lots of considerations.
Note2: this will also make analysis tools work correctly on joined layers.

[Bud]

@JonathanNeal 

Would you generate the row number field values using SQL or using ArcObjects? If SQL, I wonder if you would need the FGDB SQL folks to add row number functionality to FGDB SQL: ROW_NUMBER function in FGDB SQL

[Bud]

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.

[Bud]

@JonathanNeal and @JillScholz_esri 

Make Query Table has functionality that lets us generate an OBJECTID field on the fly in feature layers and table views. The OBJECTID values are truly unique, even if the relationship of the "join" is one-to-many.

Make Query Table (Data Management) > Key Field Options parameter

Generate a key field—If no key fields have been specified, an Object ID field that uniquely identifies each row in the output table will be generated.

Could the same mechanism be used in the attribute table of a proper one-to-many join via Add Join?

Would that help solve the 1:M join selection issue (duplicate OBJECTIDs)?
BUG-000154798: The 'Select By Attributes' tool does not select only records with specified field val...

[JonathanNeal]

@Bud If you are after certain selected records in the related table, consider this approach.

1. Use the Add Relate tool (or make a relationship class if you want it permanent)
2. Enable the layer property Automatically select related records—ArcGIS Pro | Documentation
3. Do the selection
4. On the related table, You can highlight records you wish to process.