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?

[Anonymous User]

Original User: BelowZero8

No one has any ideas?  This has to be possible.

I've found that IDisplayRelationshipClass gives access to members that help set up a join, but I can't get it to work properly.  Say I have this data(csv format):

Left
J_Fld, Name
1, One match
2, No match
3, Two matches

Right
J_Fld, Name
1, John 1
3, John 2
3, John 3

I roughly have this code:

IFeatureClass left = GetFCByName("Left");
IFeatureClass right = GetFCByName("Right");
IFeatureLayer left_FL = new FeatureLayer() { FeatureClass = left };
IFeatureLayer right_FL = new FeatureLayer() { FeatureClass = right };

IDisplayRelationshipClass dispRelClass = (IDisplayRelationshipClass)left_FL;

IMemoryRelationshipClassFactory memRelClassFact = new MemoryRelationshipClassFactory();
IRelationshipClass relClass = memRelClassFact.Open("Join", left_FL.FeatureClass, "J_Fld", right_FL.FeatureClass, "J_Fld", "forward", "backward", esriRelCardinality.esriRelCardinalityOneToOne);

dispRelClass.DisplayRelationshipClass(relClass, esriJoinType.esriLeftOuterJoin);

Type rqtFactoryType = Type.GetTypeFromProgID("esriGeodatabase.RelQueryTableFactory");
IRelQueryTableFactory rqtFactory = (IRelQueryTableFactory)Activator.CreateInstance(rqtFactoryType);
ITable relQueryTable = (ITable)rqtFactory.Open(relClass, true, null, null, "", true, false);

The esriLeftOuterJoin even says "All source rows are included.  However, when I set up a cursor on the table I still cannot get access to the "No Match" from the left table.  The really weird thing though, is that if I do a RowCount it tells me I have 3, but when I loop through the cursor it only returns two rows.

Someone have any idea at all.  Please help!

[JohnStephens · Accepted solution]

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";