idea Choose if join will be one-to-first or one-to-many in ArcGIS Pro Ideas

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

Bud — Wed, 19 Mar 2025 13:55:28 GMT

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?

Re: Choose if join will be 1:1 or 1:Many

SSWoodward — Wed, 04 Jan 2023 00:57:55 GMT

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.

Re: Choose if join will be 1:1 or 1:Many

Bud — Fri, 10 Feb 2023 17:26:20 GMT

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.

Re: Choose if join will be 1:1 or 1:Many

Bud — Thu, 11 Jan 2024 06:54:59 GMT

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?

Re: Choose if join will be 1:1 or 1:Many

Bud — Wed, 19 Mar 2025 13:56:27 GMT

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.

Video:

(view in My Videos)

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.

Edit: Select by Attributes is greyed out when this approach is used. So it is not useful in most cases.

Re: Choose if join will be one-to-first or 1:Many

KyleC — Thu, 14 Mar 2024 18:21:25 GMT

ENH-000141702 will be addressed in Pro 3.3.

Re: Choose if join will be one-to-first or 1:Many

Bud — Thu, 14 Mar 2024 18:43:40 GMT

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?

Re: Choose if join will be one-to-first or 1:Many - Status changed to: In Product Plan

DrewFlater — Mon, 08 Apr 2024 21:59:52 GMT

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

Re: Choose if join will be one-to-first or 1:Many

DrewFlater — Mon, 08 Apr 2024 22:01:42 GMT

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/1237557

Re: Choose if join will be one-to-first or 1:Many

Bud — Thu, 09 May 2024 17:24:14 GMT

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.

Re: Choose if join will be one-to-first or 1:Many

Bud — Thu, 09 May 2024 17:31:12 GMT

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

Re: Choose if join will be one-to-first or 1:Many - Status changed to: Implemented

KoryKramer — Fri, 10 May 2024 16:50:35 GMT

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.

Re: Choose if join will be one-to-first or 1:Many

Bud — Sun, 09 Mar 2025 08:44:49 GMT

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

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

Bud — Wed, 19 Mar 2025 13:58:11 GMT

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

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

DrewFlater — Wed, 19 Mar 2025 20:40:46 GMT

@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.

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

Bud — Mon, 31 Mar 2025 16:02:21 GMT

@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?

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

DrewFlater — Wed, 19 Mar 2025 21:08:42 GMT

@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.

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

Bud — Mon, 31 Mar 2025 16:08:22 GMT

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

Bud — Thu, 20 Mar 2025 16:25:51 GMT

@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.

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

DrewFlater — Thu, 20 Mar 2025 21:54:36 GMT

@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.