Join tables

[GregRieck]

Hello,

How do you create a join between multiple tables in code? I have four tables I need to join through code. I've successfully completed the same joins using ArcMap. The tables have relationship classes defined. Can anyone provide me with some sample code?

G

[NeilClemmons]

You join multiple tables to a layer the same way you join a single table to a layer except you need to use a reference to the layer's DisplayFeatureClass instead of FeatureClass.  There is a VB6 example in the developer help topic for IDisplayRelationshipClass.  The call to the Open method would need to be modified to use IGeoFeatureLayer.DisplayFeatureClass.  Call this code for each table you want to join.  You might also have to fully qualify the feature class field name after the first join has been performed.  Note the order of the Open method's parameters.  The Table reference and its key field are passed in first followed by the DisplayFeatureClass reference and its key field.  If you pass them in backwards the join will not work and you will most likely get a draw error in ArcMap.  Furthermore, ArcMap doesn't fully support one-to-many relationships.  If there is more than one match in the table for a feature in the feature class then only the first match will be included in the join (this is because the feature class table can only have one row per feature).

Note, if you already have relationship classes defined for joining each table to the feature class then you should be able to simply call IDisplayRelationshipClass.DisplayRelationshipClass for each one.

[GregRieck]

Neil,

Thanks for responding. Yep, my relationship classes are one to many. What is the best way to query when you have to join multiple tables to obtain the results you are looking for? I've been playing around with IQueryDef2 and not having much luck there either. The PrefixClause for Distinct is causing issues. Is there a way to use a "normal" SQL type query that supports Distinct, Order By, Group By, Union etc.? Also, is it possible to dump query results to a table either created as part of the query results or a virtual table? I'm looking for more advanced querying than what I've been able to find in the general help documents. Can you help point me in the right direction?

G

[NeilClemmons]

ArcObjects really doesn't have great support for advanced querying.  You may want to look into querying the database directly using ADO or something like that.  Of course if your data is not in a database such as Access, SQL Server, or Oracle you can't do that and if your query depends on the feature geometry then that limits you as well.  For one-to-many relationships in ArcObjects you would need to use a Relate instead of a Join.  You can access related objects through IRelationshipClass.GetObjectsRelatedToObject and other similar methods on the interface.  If you're looking to create a table to store query results you might be able to do that using IWorkspace.ExecuteSQL but I've never tried it.