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).
- First add your table, then your feature class to the Input tables box.
- 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.
- 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.
- Give your Table a name.
- Select the option to use Virtual Key Fields.
- Click OK.
- 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