nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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?

Find more posts tagged with

Geoprocessing

Implemented

Status: Implemented

Comments

KeithAddison1

I was waiting for this one, thanks!

Bud

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

Drew Flater

@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

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

Bud

Drew Flater

@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

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

Drew Flater

@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

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

Bud

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

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

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

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.

Drew Flater

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

Drew Flater

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

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?

KyleC

ENH-000141702 will be addressed in Pro 3.3.

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.

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.

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

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?

Bud

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.

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.