A quick tip on performing a 1:M join

Have you ever had a 1:M relationship but did not want to perform a relate? You might consider using the Make Query Table tool to accomplish this task. Typically when performing a 1:M join, only the first record will be joined and the subsequent records will not. In instances such as this, I often recommend using a relate or relationship class instead of a join. However, using the Make Query Table tool is another option for performing this task and can also be an effective way of querying out matching records from two separate tables or feature classes.

You can access this tool by navigating to the ArcToolbox > Data Management Tools > Layers and Table Views >  Make Query Table. To perform this, your tables and/or feature classes must be in the same location (the same file, personal, or SDE geodatabase).

Steps

  1. First add your table, then your feature class to the Input tables box.
  2. Select the Fields you want in the resulting table view.
    *If you want to include geometry in the output and not just a table view, be sure to select the shape fields.
  3. Click SQL. Write a statement similar to the one in the following graphic. This statement is basically saying, give me all records that match between the table and the feature class. Click OK.
  4. Give your Table a name.
  5. Select the option to use Virtual Key Fields.
  6. Click OK.
  7. Once this finishes running, export the output to a new feature class, as this is only an event layer and will not save outside of the MXD.

For more on this tool, please visit the Resource Center help page for the Make Query Table tool.

Lucas D. – Desktop Support Analyst


This entry was posted in ArcGIS for Desktop and tagged , , , , . Bookmark the permalink.

Leave a Reply

5 Comments

  1. atizzard says:

    Would you be able to type out the SQL syntax from the above image? The screen capture is not clear enough to see and it does not link to a larger image like the toolbox capture.

  2. dk10 says:

    Can you please give us an SQL example. The image you provided is too small to read and not high enough quality to zoom into. Thanks

    Darryl Klassen

  3. ldanzinger says:

    the syntax in the example is:
    tracts.Name = Store2.Tract

    it is following this formula:
    Table_1_Name.Field_1_Name = Table_2_Name.Field_2.Name

    To do this, just double click the name of the field from each table and add an equal sign in between the two. This will match all records between the two tables that have the same values in those fields. As stated in the blog, you will have issues verifying the expression at version 10 (it will say it is not valid syntax, but it is). Go ahead and click OK to apply the Expression to the tool.

    Luke

  4. alnesbit says:

    brilliant! Thank you!!