Knowledge Base - Technical Articles


Technical Article   HowTo:  Create a one-to-many join in ArcMap

Article ID: 37544
Software:  ArcGIS - ArcEditor 9.0, 9.1, 9.2, 9.3, 9.3.1, 10 ArcGIS - ArcInfo 9.0, 9.1, 9.2, 9.3, 9.3.1, 10 ArcGIS - ArcView 9.0, 9.1, 9.2, 9.3, 10
Platforms:  Windows 2000, XP, 2003Server, Vista, Win 7

Summary

Instructions provided describe how to create a table with a one-to-many mapping to another table.

Procedure

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:

  • Make Query Table (ArcToolbox > Data Management Tools > Layers and Table Views)

    This tool allows feature class attribute tables and non-spatial tables to be combined, but all 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.

    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.

  • Spatial Join (ArcToolbox > Analysis Tools > Overlay)

    This tool adds fields from the Other layer's attribute table to the Main layer's table based upon a spatial relationship (Intersects, Contains, Is_Within, and 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.

Related Information


Created: 1/26/2010
Last Modified: 6/29/2010

Article Rating: (2)
If you would like to post a comment, please login

Comments

By jstreeb - 01/27/2014 3:26 PM

The article needs to be updated.

The info in the article worked, but the links in "related information" link to the 9.3 help documents. Maybe also make it a bit more clear where and how the SQL statement is entered in the Make Query Table tool.

Rating:

By mathomas7 - 12/31/2013 6:11 AM

I would like to see a new article that discusses the topic outlined below.

Why has this functionality been removed in 10.2?

Rating: