Joining data

Summary
Joining data appends the fields from one or more tables to another table. ArcGIS provides several ways to join data. The appropriate method depends on the data sources and the cardinality of the data. This topic describes the available options and how to decide which option is best for an application.

In this topic 

Query tables

Query tables are virtual tables that represent queries involving one or more tables from the same geodatabase or Object Linking and Embedding database (OLE DB) data source. 
When building a query table, an ObjectID column can be manufactured based on a primary key. This allows one-to-many joins in the table with an ObjectID column; therefore, supports selections.
When the query table is created, it is returned as a read-only table or feature class depending on whether or not a shape column is involved. It can then be added to ArcMap as a layer or used as any other read-only table or feature class. The query can be persisted in a layer file or a map document. If changes are made to the tables involved in the query, they are reflected in the query table since it is a virtual table.
To get a query table, create a QueryDef object, which defines the join. The IQueryDef.Evaluate method can then be called to get a cursor of the join results. 
Note that a QueryDef (and subsequently, query tables) will only return rows that have been saved to the database. If an application is in an edit session, the changes made during that edit session will not be reflected in the results of a QueryDef.
The following code example shows how to join a streets feature class (using the OBJECTID field as the primary key) and a table (altname) that holds alternative names for streets (using the JOINID field as the foreign key):
[C#] 
// Create the query definition.
IQueryDef queryDef = featureWorkspace.CreateQueryDef();

// Provide a list of tables to join.
queryDef.Tables = "streets, altname";

// Set the subfields and the WhereClause (in this case, the join condition).
queryDef.SubFields = "streets.NAME, streets.TYPE, altname.ST_NAME, altname.ST_TYPE";
queryDef.WhereClause = "streets.OBJECTID = altname.JOINID";

// Get a cursor of the results and find the indexes of the fields to display.
using (ComReleaser comReleaser = new ComReleaser())
{ 
  ICursor cursor = queryDef.Evaluate();
  comReleaser.ManageLifetime(cursor);
  int streetsNameIndex = cursor.FindField("streets.NAME");
  int streetsTypeIndex = cursor.FindField("streets.TYPE");
  int altnameNameIndex = cursor.FindField("altname.ST_NAME");
  int altnameTypeIndex = cursor.FindField("altname.ST_TYPE");

  // Use the cursor to step through the results, displaying the names and altnames of each 
 // street.
  IRow row = null;
  while ((row = cursor.NextRow()) != null)
  {
    Console.WriteLine("Street name: {0} {1}. - Alt. name: {2} {3}.", row.get_Value(streetsNameIndex),
      row.get_Value(streetsTypeIndex), row.get_Value(altnameNameIndex), row.get_Value(altnameTypeIndex));
  }
}
[VB.NET] 
' Create the query definition.
Dim queryDef As IQueryDef = featureWorkspace.CreateQueryDef()

' Provide a list of tables to join.
queryDef.Tables = "streets, altname"

' Set the subfields and the WhereClause (in this case, the join condition).
queryDef.SubFields = "streets.NAME, streets.TYPE, altname.ST_NAME, altname.ST_TYPE"
queryDef.WhereClause = "streets.OBJECTID = altname.JOINID"

' Get a cursor of the results and find the indexes of the fields to display.
Using comReleaser As ComReleaser = New ComReleaser()
  Dim cursor As ICursor = queryDef.Evaluate()
  comReleaser.ManageLifetime(cursor)
  Dim streetsNameIndex As Integer = cursor.FindField("streets.NAME")
  Dim streetsTypeIndex As Integer = cursor.FindField("streets.TYPE")
  Dim altnameNameIndex As Integer = cursor.FindField("altname.ST_NAME")
  Dim altnameTypeIndex As Integer = cursor.FindField("altname.ST_TYPE")

  ' Use the cursor to step through the results, displaying the names and altnames of each 
  ' street.
  Dim row As IRow = Nothing
  Do While Not row Is Nothing
    Console.WriteLine("Street name: {0} {1}. - Alt. name: {2} {3}.", row.Value(streetsNameIndex), row.Value(streetsTypeIndex), row.Value(altnameNameIndex), row.Value(altnameTypeIndex))
    row = cursor.NextRow()
  Loop
End Using
With the QueryDef defined, the TableQueryName class can be used to create the query table. The following code example shows a function that takes a QueryDef, the geodatabase workspace containing the tables, and the name of the new query table. It also takes an argument for the comma-delimited list of key fields to use to manufacture the ObjectIDs. If this is not an option, set the makeCopy parameter (or the CopyLocally property) to true and provide an empty string for the key fields.
[C#] 
// Make the new TableQueryName.
IQueryName2 queryName2 = (IQueryName2)new TableQueryNameClass();
queryName2.QueryDef = queryDef;
queryName2.PrimaryKey = "streets.StreetID";
queryName2.CopyLocally = true;

// Set the workspace and name of the new QueryTable.
IDatasetName datasetName = (IDatasetName)queryName2;
datasetName.WorkspaceName = workspaceName;
datasetName.Name = "StreetsJoin";

// Open the virtual table.
IName name = (IName)queryName2;
ITable table = (ITable)name.Open();
[VB.NET] 
' Make the new TableQueryName.
Dim queryName2 As IQueryName2 = CType(New TableQueryNameClass(), IQueryName2)
queryName2.QueryDef = queryDef
queryName2.PrimaryKey = "streets.StreetID"
queryName2.CopyLocally = True

' Set the workspace and name of the new QueryTable.
Dim datasetName As IDatasetName = CType(queryName2, IDatasetName)
datasetName.WorkspaceName = workspaceName
datasetName.Name = tableName

' Open the virtual table.
Dim name As IName = CType(queryName2, IName)
Dim table As ITable = CType(name.Open(), ITable)
At ArcGIS 10 and later, developers joining two or more datasets from the same ArcSDE or OLE DB data source may want to consider using query classes or query cursors. For more information, see Query classes and cursors.

RelQueryTables
RelQueryTables are used to join tables from different data sources. For example, a dBASE table can be joined to a file geodatabase feature class using a RelQueryTable.
Creating a RelQueryTable requires two tables (or feature classes) and a relationship class. The relationship class can be persisted in a geodatabase or created as a temporary memory relationship class. RelQueryTables do not support one-to-many joins. See the following illustration:
RelQueryTables, like query tables, are read-only and virtual in that changes made to the base tables are reflected in the RelQueryTable.
Like workspace factories, the MemoryRelationshipClassFactory and RelQueryTableFactory classes are singleton Component Object Model (COM) classes and should not be instantiated in .NET using the new keyword; instead, they should be instantiated using the Activator.CreateInstance method with Type instances created using Type.GetTypeFromProgID. For more information, see Interacting with singleton objects.
The following code example shows how to join a feature class of parcels and a table of owners to create a RelQueryTable:
[C#] 
// Build a memory relationship class.
Type memRelClassFactoryType = Type.GetTypeFromProgID("esriGeodatabase.MemoryRelationshipClassFactory");
IMemoryRelationshipClassFactory memRelClassFactory = (IMemoryRelationshipClassFactory)Activator.CreateInstance(memRelClassFactoryType);
IRelationshipClass relationshipClass = memRelClassFactory.Open("ParcelsOwners", parcelsFeatureClass,
       "PARCEL_ID", (IObjectClass)ownersTable, "PARCEL_ID", "Is Owned By", "Owns", esriRelCardinality.esriRelCardinalityOneToOne);

// Open the RelQueryTable as a feature class.
Type rqtFactoryType = Type.GetTypeFromProgID("esriGeodatabase.RelQueryTableFactory");
IRelQueryTableFactory rqtFactory = (IRelQueryTableFactory)Activator.CreateInstance(rqtFactoryType);
ITable relQueryTable = (ITable)rqtFactory.Open(relationshipClass, false, null, null, String.Empty, false, false);
[VB.NET] 
' Build a memory relationship class.
Dim memRelClassFactoryType As Type = Type.GetTypeFromProgID("esriGeodatabase.MemoryRelationshipClassFactory")
Dim memRelClassFactory As IMemoryRelationshipClassFactory = CType(Activator.CreateInstance(memRelClassFactoryType), IMemoryRelationshipClassFactory)
Dim relationshipClass As IRelationshipClass = memRelClassFactory.Open("ParcelsOwners", parcelsFeatureClass, "PARCEL_ID", CType(ownersTable, IObjectClass), "PARCEL_ID", "Is Owned By", "Owns", esriRelCardinality.esriRelCardinalityOneToOne)

 ' Open the RelQueryTable as a feature class.
Type rqtFactoryType = Type.GetTypeFromProgID("esriGeodatabase.RelQueryTableFactory")
Dim rqtFactory As IRelQueryTableFactory = CType(Activator.CreateInstance(rqtFactoryType), IRelQueryTableFactory)
Dim relQueryTable As ITable = CType(rqtFactory.Open(relationshipClass, False, Nothing, Nothing, String.Empty, False, False), ITable)


See Also:

Querying geodatabase tables
Query classes and cursors



To use the code in this topic, reference the following assemblies in your Visual Studio project. In the code files, you will need using (C#) or Imports (VB .NET) directives for the corresponding namespaces (given in parenthesis below if different from the assembly name):
Development licensing Deployment licensing
ArcGIS for Desktop Basic ArcGIS for Desktop Basic
ArcGIS for Desktop Standard ArcGIS for Desktop Standard
ArcGIS for Desktop Advanced ArcGIS for Desktop Advanced
Engine Developer Kit Engine