Make Query Table (Data Management Tools)

Summary

Applies an SQL query to a database, and the results are represented in either a layer or table view. The query can be used to join several tables or return a subset of fields or rows from the original data in the database.

This tool accepts data from a geodatabase or an OLE DB connection.

Usage

The layer that is created by the tool is temporary and will not persist after the session ends unless the document is saved.
All input feature classes or tables must be from the same input workspace.
If a Shape field is added to the field list, the result is a layer; otherwise, it is a table view.
If the output result is a layer, it can be persisted to a layer file using the Save To Layer File tool or to a feature class using the Copy Features tool.
The order of the fields in the field list is the order the fields will appear in the output layer or table view.
You can provide a key field option and key fields list. This information defines how rows are uniquely identified and is used to add a dynamically generated Object ID field to the data. Without an Object ID field, selections will not be supported.
You can choose several fields from the key fields list if a combination of the fields is needed to define unique values.
If an SQL expression is used but returns no matching records, the output feature class will be empty.
Feature classes can be joined, but the fields list must contain at most one field of type geometry.
For details on the syntax for the Expression parameter, see SQL mode.
When input tables are from a file geodatabase, tables generally join in the order listed in the Input Tables parameter. For example, if Table1 is listed before Table2, Table2 will be joined by getting a row from Table1, then getting matching rows from Table2. However, if this would result in querying Table2 on a nonindexed field, and reversing the order would result in querying Table1 on an indexed field, the order will be reversed in an attempt to maximize performance. This is the sole query optimization logic used when you're using file geodatabase data with this tool. In general, joins in file geodatabases perform best when they are one-to-many or one-to-one.

Label	Explanation	Data type
Input Tables	The name of the table or tables to be used in the query. If several tables are listed, the Expression parameter can be used to define how they will be joined. The input table can be from a geodatabase or a database connection.	Table View; Raster Layer
Table Name	The name of the layer or table view that will be created.	Table View; Raster Layer
Key Field Options	Specifies how an Object ID field will be generated (if at all) for the query. Layers and table views in ArcGIS require an Object ID field. An Object ID field is an integer field that uniquely identifies rows in the data being used. Use key fields—Specified fields in the Key Fields parameter will be used to uniquely identify a row in the output table. This can be a single field or multiple fields, which, when combined, uniquely identify a row in the output table. If no fields are specified in the key fields list, the Generate a key field option will be applied. Generate a key field—If no key fields have been specified, an Object ID field that uniquely identifies each row in the output table will be generated. No key field—No Object ID field will be generated. Selections will not be supported for the table view. Note: If there is an existing Object ID field, it will be used even if this option is chosen.	String
Key Fields (Optional)	A field or combination of fields that will be used to uniquely identify a row in the query. This parameter is used only when the Key Field Options parameter is set to Use key fields.	Field
Fields (Optional)	The fields that will be included in the layer or table view. If an alias is set for a field, this is the name that appears. If no fields are specified, all fields from all tables are included. If a `Shape` field is added to the field list, the result is a layer; otherwise it is a table view. Value table columns: Field—The fields to include in the layer or table view. Field Alias—The optional field alias.	Value Table
Expression (Optional)	An SQL expression used to select a subset of records.	SQL Expression

arcpy.management.MakeQueryTable(in_table, out_table, in_key_field_option, {in_key_field}, {in_field}, {where_clause})

Name	Explanation	Data type
in_table [in_table,...]	The name of the table or tables to be used in the query. If several tables are listed, the `where_clause` parameter can be used to define how they will be joined. The input table can be from a geodatabase or a database connection.	Table View; Raster Layer
out_table	The name of the layer or table view that will be created.	Table View; Raster Layer
in_key_field_option	Specifies how an Object ID field will be generated (if at all) for the query. Layers and table views in ArcGIS require an Object ID field. An Object ID field is an integer field that uniquely identifies rows in the data being used. `USE_KEY_FIELDS`—Specified fields in the `in_key_field` parameter will be used to uniquely identify a row in the output table. This can be a single field or multiple fields, which, when combined, uniquely identify a row in the output table. If no fields are specified in the key fields list, the `ADD_VIRTUAL_KEY_FIELD` option will be applied. `ADD_VIRTUAL_KEY_FIELD`—If no key fields have been specified, an Object ID field that uniquely identifies each row in the output table will be generated. `NO_KEY_FIELD`—No Object ID field will be generated. Selections will not be supported for the table view. Note: If there is an existing Object ID field, it will be used even if this option is chosen.	String
in_key_field [in_key_field,...] (Optional)	A field or combination of fields that will be used to uniquely identify a row in the query. This parameter is used only when the `in_key_field_option` parameter is set to `USE_KEY_FIELDS`.	Field
in_field [[field, {alias}],...] (Optional)	The fields that will be included in the layer or table view. If an alias is set for a field, this is the name that appears. If no fields are specified, all fields from all tables are included. If a `Shape` field is added to the field list, the result is a layer; otherwise it is a table view. Value table columns: `Field`—The fields to include in the layer or table view. `Field Alias`—The optional field alias.	Value Table
where_clause (Optional)	An SQL expression used to select a subset of records.	SQL Expression

Code sample

MakeQueryTable example 1 (Python window)

The following Python window script demonstrates how to use the MakeQueryTable function in immediate mode.

import arcpy
arcpy.env.workspace = "C:/data/data.gdb"
arcpy.management.MakeQueryTable(["Counties","codemog"], "queryout", "ADD_VIRTUAL_KEY_FIELD", "",
                                [["Counties.OBJECTID", 'ObjectID'], ["Counties.NAME", 'Name'],
                                 ["codemog.Males", 'Males'], ["codemog.Females", 'Females']],
                                "Counties.FIPS = codemog.Fips and Counties.STATE_NAME = 'California'")

MakeQueryTable example 2 (stand-alone script)

The following script is an example of how to use the MakeQueryTable function in the Python scripting environment.

# MakeQueryTableOLEDB.py
# Description: Create a query table from two OLE DB tables using a limited set of
#               fields and establishing an equal join.

# Import system modules
import arcpy

# Local variables...
tableList = ["c:/Connections/balrog.odc/vtest.COUNTIES",\
             "c:/Connections/balrog.odc/vtest.CODEMOG"]

fieldList = [["vtest.COUNTIES.OBJECTID", 'ObjectID'], ["vtest.COUNTIES.NAME", 'Name']\
             ["vtest.CODEMOG.Males", 'Males'], ["vtest.CODEMOG.Females", 'Females']]
whereClause = "vtest.COUNTIES.FIPS = vtest.CODEMOG.Fips" +\
              "and vtest.COUNTIES.STATE_NAME = 'California'"
keyField = "vtest.COUNTIES.OBJECTID"
lyrName = "CountyCombined"

# Make Query Table...
arcpy.management.MakeQueryTable(tableList, lyrName,"USE_KEY_FIELDS", keyField, fieldList, whereClause)

# Print the total rows
print(arcpy.management.GetCount(lyrName))

# Print the fields
fields = arcpy.ListFields(lyrName)
for field in fields:
    print(field.name)

# Save as a dBASE file
arcpy.management.CopyRows(lyrName, "C:/temp/calinfo.dbf")

Environments

Current Workspace

Licensing information

Basic: Yes
Standard: Yes
Advanced: Yes