Choose if join will be one-to-first or one-to-many

[Bud]

ArcGIS Pro 3.0.3; mobile geodatabase.

When creating a join via Add Join:

For cases where the join table has duplicate rows, it would be helpful if we could choose whether the join will be one-to-first (1:1) or 1:M.

Result:

Can an option be added to Add Join to explicitly choose either one-to-first or 1:M?

Related: Join — Control what related record gets used

[KoryKramer]

This is implemented in ArcGIS Pro 3.3. See Your Ideas in ArcGIS Pro 3.3 for a full list of all user ideas you can look forward to! And be sure to check out the release blog and what’s new help topic and video to learn more about all the new features and functionality in the release.

[Bud]

One-to-first join doesn't use related record with lowest OBJECTID

[Bud]

FYI - In Pro 3.3.5 (Oracle 19c; 11.3 EGDB), the one-to-many option is missing from the Join Operation parameter.

[DrewFlater]

@Bud thanks for your comments. For enterprise database sources there currently isn't a mechanism to match to the lowest ObjectID, we're in discussion with the enterprise geodatabase team for ideas how to resolve this. 

The one-to-many option is only available if the input and join tables are in the same database, since the joining happens at the database level. 

[Bud]

@DrewFlater Thanks for following up.

If the related record with the lowest OBJECTID is not used, then what related record does get used? Is it deterministic or arbitrary?

[DrewFlater]

@Bud databases like SQL Server, Oracle, others will be random when there is no SORTBY provided. Sometimes it might be one record then change to another tomorrow. 

[Bud]

Related post: Stack Overflow - Why aren't rows returned in the order of insertion?

[Bud]

@DrewFlater 

The one-to-many option is only available if the input and join tables are in the same database, since the joining happens at the database level.

FYI

Esri Case #03864968 - Pro 3.4.3 - 1:M option missing from Add Join > Join Operation parameter

Existing bug report:
BUG-000170631: Tables in mobile geodatabase cannot perform a 'Join one to many' using the Add Join tool in ArcGIS Pro.

The above also applies to enterprise geodatabase feature classes. As you noted, by default, not selecting any option will result in the software attempting a one to many join. However, the option is not there for choosing. 

[DrewFlater]

@Bud BUG-000170631 is something we are still trying to improve, but the fact is you can perform the 1:m join by leaving the join operation blank, if the input and join tables are in the same database. This behavior is documented. But it would be better if it could be made explicit in the parameter drop down.

[Bud]

Idea: Deterministic one-to-first joins on enterprise geodatabase data