Option to Create One-to-Many Join

mpboyle

There should be an option to create a one-to-many join within Pro, as joins are currently limited to a one-to-one relationship.

Example: A single geometry with many rows in a table. I'd like to have the ability to join the geometry and keep ALL rows from the joined table, not just one.

Bud · ‎12-07-2022

Check out the answer I have in this post: FGDB: Propagate duplicate features via 1:M join to related table

The docs seem to suggest that’s already possible, under specific circumstances.

Add Join (Data Management) :

Solution: Make sure both FCs/tables have ObjectIDs and are in the same geodatabase.

mpboyle · ‎12-08-2022

@Bud --- I could see this being a workaround, but it would only work for point geometry.

Bud · ‎12-10-2022

@mpboyle I think ArcGIS Pro 3.0.3 already does what you want. Adding a join can create a 1:M join.

Before:

No join
Three features

After:

Joined to a table that has duplicates. The join is based on the common ASSET_ID.
Now, there are 4 features. ASSET_ID #3 has been duplicated, since the related table has duplicate rows for that asset.

Make sure both FCs/tables have ObjectIDs and are in the same geodatabase.

KoryKramer · ‎03-10-2023

I agree with @Bud on this one - this has been possible since the early days of Pro (if not 1.0). He's already pointed to https://pro.arcgis.com/en/pro-app/latest/tool-reference/data-management/add-join.htm

@mpboyle have you been able to get the result you need? You may need to be working in a geodatabase and not shapefiles - maybe @JonathanNeal can offer additional details if needed but it seems like it is possible to do what you're requesting.

KoryKramer · ‎03-10-2023

JonathanNeal · ‎03-13-2023

@mpboyle (Or others with similar interests):
If you wish to rework the initial idea a little, there could be future plans to support one-to-many joins between Esri datatypes (.sde, feature services and .gdb) even if the input and join table are not in the same workspace.

This is not in the short term. But, a support investigation has interest here is ENH-000151081.
Our Feature Service team is/may be looking into supporting one-to-many joins using feature service layers. Unsure their status, timeline or technical challenges. But, if they can do it natural follow-up will be for other Esri datatypes to support one-to-many joins.

ASIDE:
If you think our documentation can be improved, at the bottom of the help there is a link to click, that I do read: https://pro.arcgis.com/en/pro-app/latest/tool-reference/data-management/add-join.htm

Summary of the doc:
Add Join does support one-to-many when the following two conditions are met:

The dataType must have objectIds
The data must be within the same workspace. (Shape files are excluded)

Details on why:
The internal tool IRelQueryTable has two modes for Joining data together:

Client side - A one-to-first join is done, which matches ArcMap.
First is determined by how the data format is read, which is usually ordered by objectId or record order in text base data. Client side joins are used when both conditions of a one-to-many are not met.
Server Side - The workspace manages the join, so in these cases our File Geodatabase (Or other databases) will do the one-to-many join.

KoryKramer · ‎04-20-2023

Closing this idea as there was not enough clarity about the request (See comments).

If interested in a 1:M join across different workspaces, see https://community.esri.com/t5/arcgis-pro-ideas/1-m-join-input-and-join-table-in-different/idi-p/1279...