How to perform multi-table Join using QueryDef

672
5
Jump to solution
11-20-2020 07:00 AM
DHuantes
New Contributor III

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.

 

0 Kudos
1 Solution

Accepted Solutions
DHuantes
New Contributor III

@ChristopherKoenig and @KimberlyMcCarty   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",
};

 

 

View solution in original post

0 Kudos
5 Replies
AashisLamsal1
Esri Contributor

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  -

  AashisLamsal1_0-1606160189766.pngAashisLamsal1_1-1606160200063.png

AashisLamsal1_2-1606160210881.png

 

 

 

 

 

 

Example: 1

AashisLamsal1_5-1606160454112.png

Example: 2

AashisLamsal1_3-1606160238321.png

Output:

AashisLamsal1_4-1606160305179.png

0 Kudos
DHuantes
New Contributor III

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.  

devenv_aWaAN5rt66.png

 

0 Kudos
KimberlyMcCarty
Esri Contributor

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.

0 Kudos
ChristopherKoenig
Esri Contributor

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.

0 Kudos
DHuantes
New Contributor III

@ChristopherKoenig and @KimberlyMcCarty   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",
};

 

 

0 Kudos