FGDB: Propagate duplicate features via 1:M join to related table

Bud — Sat, 03 Dec 2022 17:17:21 GMT

In a FGDB, I have:

A point FC with a unique ID field.
A related table that has the ID field as a foreign key, of sorts.
The relationship is: 1:M (one point feature to many related table records).

I want to export a copy of the FC that brings in the information from the related records. So the challenge is: how to handle the 1:M relationship?

Options:

Join from the FC to the table using an in-map join in the attribute table. In-map attribute table joins get the first arbitrary related record that it finds (“one-to-first”), and ignores the rest of the related records. So that's not quite what I want.
Or, pivot the related records into columns, making the relationship 1:1.
Or, create duplicate points for each related record.

For option #3, how can I propagate duplicate features for each related record?

If I were working in pure SQL, I'd simply use a LEFT JOIN from the FC to the table. But I don't think I can write full-blown SQL queries with JOIN clauses on FGDB data (i.e. Query Layers).

Thanks.

Re: FGDB: Propagate duplicate features via 1:M join to related table

Bud — Sat, 10 Dec 2022 14:59:58 GMT

It looks like the Add Join (Data Management) geoprocessing tool might do what I want:

Related:

Add Join will not create a 1:M join
- Solution: Make sure both FCs/tables have ObjectIDs and are in the same geodatabase.
Option to Create One-to-Many Join

Test:

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.

A different option:

Alternatively, I could add XY fields to the point FC. Then join (in-map join in attribute table) from the table to the FC. Then make an XY event layer from the result. I think that would give me the duplicated points.

topic FGDB: Propagate duplicate features via 1:M join to related table in ArcGIS Pro Questions

FGDB: Propagate duplicate features via 1:M join to related table

Re: FGDB: Propagate duplicate features via 1:M join to related table