8

I'm expecting an ArcObjects code which is equivalent to SQL left join.

For example: I have the below SQL code, it's simple join to fetch the records from the both tables based on the ChecklistId value.

SELECT  T1.ChecklistId, T1.ChecklistName, 
        T2.Latitude, T2.Longitude
FROM [dbo].[TableOne] T1 
INNER JOIN [dbo].[TableTwo] T2 ON T2.ChecklistId = T1.ChecklistId
WHERE T1.ChecklistId = @ChecklistId

I converted the above SQL query into the ArcObjects.

IQueryDef queryDef = featureWorkspace.CreateQueryDef();

queryDef.Tables = "TableOne, TableTwo";
queryDef.SubFields = "TableOne.ChecklistId, TableOne.ChecklistName, TableTwo.Latitude, TableTwo.Longitude";
queryDef.WhereClause = "TableOne.ChecklistId = '" + checklistId + "' AND TableOne.ChecklistId = TableTwo.ChecklistId";

ICursor cursor = queryDef.Evaluate();
IRow row = null;

while ((row  = cursor.NextRow()) != null)
{
    // ... get and set the values to the objects
}

I want to fetch the records even the T1.ChecklistOwner column having null values. In simple words we can say as SQL LEFT JOIN as follows:

SELECT  T1.ChecklistId, T1.ChecklistName, 
        T2.Latitude, T2.Longitude,
        T3.FullName
FROM [dbo].[TableOne] T1 
INNER JOIN [dbo].[TableTwo] T2 ON T2.ChecklistId = T1.ChecklistId
LEFT JOIN [dbo].[TableThree] T3 ON T3.UserAlias = T1.ChecklistOwner
WHERE T1.ChecklistId = @ChecklistId

How can I convert the above SQL query into ArcObjects?

1

You can do a left join using the "Tables" property.

IQueryDef queryDef = ((IFeatureWorkspace)hoja.Workspace).CreateQueryDef();
queryDef.Tables = "User LEFT JOIN Country ON User.ID = Country.ID";
queryDef.SubFields = "User.ID, User.Name, Country.Name";

Works fine with left, right and inner joins, but no idea how to make a full join.

Also, you can use several joins grouping them with parenthesis.

queryDef.Tables = "((User INNER JOIN Country ON User.ID = Country.ID) LEFT JOIN C ON User.ID = C.ID)";

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.