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


  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

About Lucas Danzinger

Lucas is a Geographer working in Product Development at Esri, focusing his time on the ArcGIS Runtime SDKs. He is an Esri Certified ArcGIS Desktop Professional and is currently most interested in building cross-platform and lightweight Qt/QML based apps using the ArcGIS Runtime SDK for Qt.
This entry was posted in ArcGIS Desktop, export and tagged , , , , . Bookmark the permalink.

Leave a Reply


  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.


  4. alnesbit says:

    brilliant! Thank you!!

  5. jrlalessi says:

    Im working with Arc 10.2.2 The input now only allows for Raster and Table inputs, so how do I create a 1:M Join. Purpose is I want to time enable a layer, the FC itself has 2118 records that show the most recent data on our web site. The FC records dont EVER move but the value displayed changes about every six months. I have a stand alone table with 52 years (104 changes of value) for each feature in the FC. So I still need to do a 1:M and this tool doesnt work either. Our data cannot be put in the cloud due to its highly sensitive nature and clearance requirements for viewing.

  6. dzuka says:

    Very clear explanation. Thank you.

  7. jhansen88 says:

    I have tried to run the make query tool and cannot get it to work. Both my polygon layer and table are in the same file gdb. I followed the instructions above and get an error message every time. I am not sure what else to do. I need to join my table that contains many records to one record in the polygon table.

    I have tried the use key option the add virtual key option. I have unchecked the boxes in the polygon layer except for the shape field. I am stumped.

    • Kory K says:

      What version of ArcGIS are you using? Is the error something like this: “There was an error with the expression. An expected Field was not found or could not be retrieved properly”

      Try removing the quotes from the Expression parameter. I know that the Expression built using the query builder includes the quotes, but there is a bug logged noting that the quotes need to be removed.

      Does that work?

      • dvansant says:

        Had this same issue on ArcGIS 10.2 and by removing the quotes from the SQL Expression it ran successfully.

      • jsalimi says:

        The other thing I would add is that even after removing the quotes, you are still getting the error like I was, try reversing the order of the fields so that the first field cited before the equal sign references the non-spatial input.

        Very good blog by the way.

    • mich8024 says:

      You may want to make sure that you leave out the quotations marks in your SQL statement. The tool doesn’t seem to run if they’re included.



  8. Lindsy says:

    Once I have a query table, how can I export this and keep the global IDs? Copy features, feature class to feature class, and table to table all delete the global IDs (these are what I use to join the data). I have a second table I need to join to the query table.

  9. giser4lyfe says:

    It would be nice if this tip referenced what version(s) of ArcMap (ArcGIS Desktop Advanced) supports this feature. From what I can tell from the How to 1:M guide, it is not supported in the latest version 10.4 of ArcGIS for Desktop Advanced. That page was last updated in May 2016. It could just be this is supported but the page was not updated.