Data Joins and Relationships

JohnStephens

Say I have the two attached tables that I want to join together in code using the "J_Fld".  As you can see, it is a one-to-many join and there will be one row in the left table that does not have a match in the right table.  I want to get a result that returns "all-results" like the join options in ArcMap (so include the one without a relationship).  If I used a QueryDefinition I get everything but the one without a relationship.

The API has code for a IRelationshipClass:

// 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);

If I change the cardinality to "esriRelCardinalityManyToMany" the return table only has 4 rows in it.  It looks like no matter what it returns a one-to-one and takes a "first case wins" approach.

I was thinking the way to go about it is IRelationshipClass.GetRelationshipForObject, but it doesn't seem to like being passed a table row as an object.

Any ideas?

right.png

left.png

JohnStephens

So, I figured it out after A LOT of trial and error.

// I found out that you cannot check "IS NULL" in a joined FC where the FCs are in a DB. // So, the CreateShapefileFromFeatClass creates a temporary shapefile IFeatureClass left = GetFCByName("Left_FC"); IFeatureClass right = GetFCByName("Right_FC"); IFeatureClass leftSAFC = CreateShapefileFromFeatClass(left, "myLeft"); IFeatureClass rightSAFC = CreateShapefileFromFeatClass(right, "myRight"); IFeatureLayer left_FL = new FeatureLayer() { FeatureClass = leftSAFC }; IFeatureLayer right_FL = new FeatureLayer() { FeatureClass = rightSAFC };  IMemoryRelationshipClassFactory memRelClassFact = new MemoryRelationshipClassFactory(); IRelationshipClass relClass = memRelClassFact.Open("Join", left_FL.FeatureClass, "J_Fld", right_FL.FeatureClass, "J_Fld",                 "forward", "backward", esriRelCardinality.esriRelCardinalityOneToOne);  // You can only wrap a feature layer in a DisplayRelationshipClass IDisplayRelationshipClass dispRelClass = (IDisplayRelationshipClass)left_FL; dispRelClass.DisplayRelationshipClass(relClass, esriJoinType.esriLeftOuterJoin);  ITable tbl = (ITable)dispRelClass;  // This where clause find where the right join field is null, AKA no match string whereClause = "myRight.J_Fld IS NULL";