How to perform multi-table Join using QueryDef

60
1
Friday
Highlighted
New Contributor II

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.

 

Reply
0 Kudos
1 Reply
Highlighted
Esri Contributor

Hi @DanielHuantes, 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

Reply
0 Kudos