How to perform multi-table Join using QueryDef

[DHuantes]

I have the following code that works fine:

await QueuedTask.Run(() =>
{
     using (Geodatabase l_myGDB = new Geodatabase(new 
     FileGeodatabaseConnectionPath(new Uri(<MyGeodatabase>))))
     {
        QueryDef queryDef = new QueryDef
        {
          Tables = "People INNER JOIN States ON People.FK_STATE_ID = States.OBJECTID",
          SubFields = "People.ObjectID,People.First_Name, People.Last_Name, People.City, State.State_Name",
        };

using (RowCursor l_rowCursor = l_laserGDB.Evaluate(queryDef,false))
{
    while(l_rowCursor.MoveNext())
    {
        //Grab all the data....
    }
}

But I can't seem to get the syntax correct to LEFT JOIN the previous resulting join with another table.  What I want to do is....

await QueuedTask.Run(() =>
{
     using (Geodatabase l_myGDB = new Geodatabase(new 
     FileGeodatabaseConnectionPath(new Uri(<MyGeodatabase>))))
     {
        QueryDef queryDef = new QueryDef
        {
          Tables = "((People INNER JOIN States ON People.FK_STATE_ID = States.OBJECTID) LEFT JOIN Homes ON People.OBJECTID = Homes.FK_PEOPLE_ID)",
          SubFields = "People.ObjectID,People.First_Name, People.Last_Name, People.City, State.State_Name, People.FK_HOME_ID, Homes.Address",
        };

using (RowCursor l_rowCursor = l_laserGDB.Evaluate(queryDef,false))
{
    while(l_rowCursor.MoveNext())
    {
        //Grab all the data....
    }
}

These are not exactly my tables names but you get the gist.   I'm trying not to duplicate states so that's why the People table as a Foreign Key to the State table.  For the third table I want people to be able to own more than one home 

I suspect it's how the Tables Property is expected to be formatted but haven't been able to figure it out... as of yet.. Thought someone could point me in the right direction.  Documentation I found seems to indicate I should be able to query multiple tables in a single Query.  I know I can do this with any standard database outside of ArcGIS Pro just trying to figure out how to do it here.   Thanks.

[Aashis]

Hi @DHuantes, I am not able to reproduce the syntax error from the provided sample. Did you see any specific error message when this happened?

In order to investigate the error, I have created 3 tables with dummy data and run QueryDef as on example1 and example 2 and both of them gave me the results  -

  

Example: 1

Example: 2

Output:

[DHuantes]

Hmmm....  Not sure why I'm getting this error then....   I'm in the middle of another part of the code right now but took a quick look and didn't see any syntax issues.  I ended up doing multiple queries as workaround but if I can get this to work it would clean up the code.   Thanks... At least now I know it should be possible.  I'll try replicate the problem as you did independent of my Add-In  to see what's unique about my add-in.  

[KimberlySaballett]

I was able to reproduce your error with the above solution and fix it by removing the parentheses in the SQL expression. SQL is executed from left to right so the parentheses here were unnecessary.

[ChristopherKoenig]

Hi @DHuantes,

I have been working with your code from your post to see what I could find about the 'Invalid SQL' error. After further testing of this behavior I have found that as of version 2.7 of the ArcGIS Pro SDK for .NET parentheses can be used in nested SQL statements without issue.

[DHuantes · Accepted solution]

@ChristopherKoenig and @KimberlySaballett   I updated to 2.7 and any of the following Tables strings work in QueryDef for the example now.  Thank you and thanks to the development team for fixing this.

QueryDef queryDef = new QueryDef
{
  // Option 1: Tables = "People INNER JOIN States ON People.FK_STATE_ID = States.OBJECTID LEFT JOIN Homes ON People.OBJECTID = Homes.FK_PEOPLE_ID",
  // Option 2: Tables = "(People INNER JOIN States ON People.FK_STATE_ID = States.OBJECTID) LEFT JOIN Homes ON People.OBJECTID = Homes.FK_PEOPLE_ID",
  // Option 3:
  Tables = "((People INNER JOIN States ON People.FK_STATE_ID = States.OBJECTID) LEFT JOIN Homes ON People.OBJECTID = Homes.FK_PEOPLE_ID)",
  SubFields = "People.OBJECTID,People.First_Name, People.Last_Name, People.City, States.Abbreviation, Homes.Address",
};