How To: Create a one-to-many join in ArcMap
Instructions provided describe how to create a table with a one-to-many mapping to another table.
A join between two tables in ArcMap can be done only with a one-to-one or many-to-one relationship between the 'Main' table and the 'Other' table (whose attributes are being joined to the Main table). For each record in the Main table, if there are multiple matching records in the Other table, only the first matching record from Other is joined.
In some cases, the desired outcome is to have a record in the Main table for every matching record in the Other table, which means creating duplicate records in the Main table. There are two possible methods to accomplish this, as follows:
Method 1: Use the Make Query Table geoprocessing tool
- In ArcToolbox, expand Data Management Tools > Layers and Table Views.
- Double-click the Make Query Table tool. This tool allows feature class attribute tables and non-spatial tables to be combined, but all the tables must reside in the same geodatabase.
Relationships can be specified among multiple tables by listing multiple conditions, such as:
<featureclass_X>.<field_A> = <table_Y>.<field_B> AND <table_Y>.<field_C> = <table_Z>.<field_D> and so on.
- To specify the relationships between pairs of fields, enter a SQL expression in the Expression field. Rather than type the expression, click the SQL button at the right of the field to open the Query Builder dialog.
The fields of each table are shown in the top listbox, in the format <tablename>.<fieldname>.
- Double-click a field name to insert it into the editing area.
- Click the other operator syntax buttons (equals, Is, And, Not, and so forth) in the dialog to add operators to the expression. Build the expression similar to an ArcMap layer definition query, but here it is possible to compare fields in one table with fields in other tables.
If the first input to the tool is a feature class, then the output structure is a feature class, otherwise, the output structure is a table. The first input is the table to which attributes of all other tables are added. Additionally, the first input's records are duplicated as necessary to match every applicable record in the other tables.
In this tool, be sure to specify a unique key field in each participating table so that the final output table has a unique index for every record.
Method 2: Use the Spatial Join geoprocessing tool
The Spatial Join (ArcToolbox > Analysis Tools > Overlay) tool adds fields from the Other layer's attribute table to the Main layer's table based upon a spatial relationship (such as Intersects, Contains, Is_Within, or Closest) between the features of the two layers.
The join operation can be one-to-one or one-to-many. If the join operation is one-to-many, the output feature class can contain duplicated records from the Main feature class to match corresponding records in the Other feature class.