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

[Bud]

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:

1. 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.
2. Or, pivot the related records into columns, making the relationship 1:1.
3. 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.

[Bud · Accepted solution]

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.