Choose if join will be one-to-first or 1: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

[SSWoodward]

Thanks for the Idea @Bud I've changed the label on this from Geodatabase to Geoprocessing to make sure it gets in front of the correct team. 

[Bud]

Related:

ENH-000141702: Please allow users to decide whether the Add Join tool in ArcGIS Pro will be doing a one-to-many or one-to-first join when the data has a one-to-many cardinality.

[Bud]

Is there a way to hide the OBJECTID from the join table so that Pro is forced to do a one-to-first join?

Add Join (Data Management)

For example, use one of these tools to hide the OBJECTID?

1. Make Query Table 
2. Make Table View 
3. Make Feature Layer

[Bud]

ArcGIS Pro 3.2.1; File Geodatabase:

It looks like the answer is yes. I was able to hide the OBJECTID field using the Make Query Table tool.

When I did the join from the input table to the join table (Query Table), the resulting join is one-to-first, since the join table doesn't have an OBJECTID field.

• As mentioned, it worked in the file geodatabase example above.
• It works for mobile geodatabases too.
• The Make Query Table tool doesn't work for non-GDB Oracle databases. ERROR 000837: The workspace is not the correct workspace type.
• I haven't tried on an Oracle enterprise geodatabase yet.

Video:

I wouldn't be surprised if there are issues with that approach. It's not exactly a supported workflow. So I think this idea is still valid.

[KyleC]

ENH-000141702 will be addressed in Pro 3.3.

[Bud]

Thanks @KyleC!

With one-to-first joins, will we be able to control what related record gets used? Join — Control what related record gets used

Or will it join to an arbitrary record / the lowest ObjectID?

[DrewFlater]

The "Join one to first" Join Operation  has been developed for Add Join in ArcGIS Pro 3.3, available in Beta now. 

[DrewFlater]

In ArcGIS Pro 3.3, the "first" in the one to first join will be the record with the matching field value with the lowest Object ID. Additional sorting may be provided in a future release, based on the Idea https://community.esri.com/t5/arcgis-pro-ideas/join-control-what-related-record-gets-used/idi-p/1237...

[Bud]

Add Join (Data Management)

Optionally, set the Join Operation parameter to Join one to first to prevent duplicate object IDs.

The Join Operation parameter has three states to adjust the cardinality. The default is blank and will allow the data source to attempt a one-to-many join. The Join one to many option will work only on specific data sources that have an Object ID field. The Join one to first option will use the first match in the table, which may result in different outputs if the Object ID field is changed or the workspace the table is copied to changes. One-to-first joins are not case sensitive; one-to-many joins are case sensitive.

[Bud]

Why aren't one-to-first joins case-sensitive?