Option to Create One-to-Many Join

1878
7
12-07-2022 02:06 PM
Status: Closed
Labels (1)
mpboyle
Occasional Contributor III

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.

7 Comments
Bud
by

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) :

Bud_0-1670534772951.png

 

Related:

Add Join will not create a 1:M join

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

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

Bud
by

@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

Bud_0-1670681317954.png


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.

Bud_0-1670684201570.png

 

Bud_0-1670684337175.png

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

 

KoryKramer

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 

KoryKramer_0-1678487331252.png

@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
Status changed to: Needs Clarification
 
JonathanNeal

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

  1. The dataType must have objectIds
  2. 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:

  1. 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.
  2. 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
Status changed to: Closed

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