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 ArcGIS for Desktop Advanced 10.1, 10.2, 10.2.1, 10.2.2, 10.3, 10.3.1 ArcGIS for Desktop Basic 10.1, 10.2, 10.2.1, 10.2.2, 10.3, 10.3.1 ArcGIS for Desktop Standard 10.1, 10.2, 10.2.1, 10.2.2, 10.3, 10.3.1
Platforms:  Windows XP, Server 2003, 2000, Windows 8, Server 2012, Vista, Server 2008, Windows 7, Server 2008 R2, Windows 8.1, Server 2012 R2


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:

  • 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.

    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. -show me-

    [O-Image] QueryBuilderDialog

    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. -show me-

    [O-Image] QueryBuilderExpression

    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 (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.

Related Information

Created: 1/26/2010
Last Modified: 8/24/2015

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


By JSkinn3 - 10/06/2015 3:58 AM

Great article! It helped a lot!


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.


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?