English

How To: Create a one-to-many join in ArcGIS for Desktop without using the Make Query Table tool

Summary

The Make Query Table tool is used to achieve a one-to-many attribute join in ArcMap. However, because the join fields are set to be equal to each other in the SQL query that the tool relies on, only records that have a match in the other table are preserved in the output. For records with multiple matching records in the other table, only the first matching records are displayed.
User-added image
This is impractical when some records in a table have multiple matches in another table, or some records do not have any matches in the other table, but all records are intended to be preserved.

Procedure

The following is a workaround to perform a one-to-many attribute join for multiple matching records:

  1. Ensure both the tables or feature classes are in a file geodatabase.
  2. Create the attribute join by right-clicking the larger table or the feature class with more records, and select Join.
  3. In the Join Options field of the Join Data dialog box, be sure to choose the option, Keep all records.
User-added image
  1. Join validation errors are returned. This is expected.
    User-added image
  2. Right-click the feature class with the join, and export the feature class to a new file geodatabase feature class.
  3. Open the attribute table of the exported feature class, and note that there are more records, instead of only the first matching record being displayed. Each matching record is recorded in a unique line in the table. Unlike the Make Query Table tool's output, records in the target table that do not have a match in the joined table are still present, with null values in the fields from the joined table.
Note:
Prior to exporting, unexpected behavior is observed in the feature class with the active one-to-many join. It is not recommended to work with this table. It is best to export the table to a new feature class, and remove joins from the original table.

Related Information

ArcMap: Make Query Table
ArcMap: Deciding between relationship classes, relates, and joins
ArcMap: About joining and relating tables