I have two feature classes in a File Geodatabase that I would like to join based on multiple fields. I've searched this site and Google and all I have come up with was to use the Make Query Table tool. I've tried this, but I keep getting an SQL error. My SQL is pretty poor and I'm pretty sure I'm missing something.

I am aware I can create a new field and concatenate the values from my fields, but I would like to avoid this, if possible.

I'm using something that looks like this:

(Table1.Field1 = Table2.Field1) AND (Table1.Field2 = Table2.Field2) AND (Table1.Field3 = Table2.Field3)

When I verify the query, I get an error that says:

There was an error with the expression. 
An Invalid SQL statement was used.
An invalid SQL statement was used. [Table1]

Also, if someone has another solution that doesn't use this tool, I'm happy to hear about it.

  • Try joining Table 1 to Table 2, then joining Table 3 to the result of that. It would also help you if you used query builder ([sql] button in the Make Query Table tool) to verify the syntax.
    – nagytech
    Commented Aug 9, 2012 at 0:16
  • Note, there are only two tables but three fields. I am using query builder and the error I get appears when I click on Verify. The join is effectively joining fields 1,2,3 from table 1 to fields 1,2,3 from table 2.
    – Fezter
    Commented Aug 9, 2012 at 0:18
  • I didn't read the post very well, it makes more sense now.
    – nagytech
    Commented Aug 9, 2012 at 1:08
  • Just as a reminder: also make sure the classes or tables are part of the same geodatabase AND make sure the keyfields on which the join will be operated are of the same type.
    – user41285
    Commented Nov 29, 2014 at 13:21

2 Answers 2


Your syntax is incorrect.


If you read the Make Query Table sidebar help:

Expression (optional)

An SQL expression used to select a subset of records. The syntax for the expression differs slightly depending on the data source. For example, if you're querying file or ArcSDE geodatabases, shapefiles, coverages, dBase, or INFO tables, enclose field names in double quotes:


If you're querying personal geodatabases, enclose fields in square brackets:



That would imply the correct format as:

"Table2"."f1" = "Table1"."f1" AND "Table2"."f2" = "Table1"."f2" AND "Table2"."f3" = "Table1"."f3" 


The following python snippet will enable you to run the tool without error:

arcpy.MakeQueryTable_management("Table1;Table2","QueryTable11111","NO_KEY_FIELD","#","Table1.f1 #;Table1.f2 #;Table1.f3 #;Table2.f1 #;Table2.f2 #;Table2.f3 #","/\Table1/\./\f1/\ = /\Table2/\./\f1/\ AND /\Table1/\./\f2/\ = /\Table2/\./\f2/\ AND /\Table1/\./\f3/\ = /\Table2/\./\f3/\")


enter image description here

  • I am aware of this method, as I mentioned in my question. I'm looking for another way, if there is one.
    – Fezter
    Commented Aug 9, 2012 at 1:26
  • 3
    Although you are aware of the solution, and is, in fact, a solution to your problem, others may not be.
    – nagytech
    Commented Aug 9, 2012 at 1:37
  • @Fezter I've updated my answer, please have a look.
    – nagytech
    Commented Aug 9, 2012 at 2:00
  • thanks for the updated answer. I used the query builder, so I assumed it would automatically put it in the correct syntax for me. I added the quotes and it did get a bit further than before. I'm able to run the tool but I get the following error now: ERROR 999999: Error executing function. An expected Field was not found or could not be retrieved properly. [QueryTable] An expected Field was not found or could not be retrieved properly. [Table2.Field1] An expected Field was not found or could not be retrieved properly. [Table2.Field1] Failed to execute (MakeQueryTable).
    – Fezter
    Commented Aug 9, 2012 at 2:10
  • 1
    I've taken back the downvote for your effort. Much appreciated.
    – Fezter
    Commented Aug 9, 2012 at 2:11

(FGDB 10.2 & ArcMAP 10.2) Perhaps opening up an old question - but I think this is relevant. The Make Query Table SQL creator throws an error when you verify, even if your syntax is correct. I created my query without any double-quotes or brackets, simply

Table1.Field1 = Table2.Field1 AND Table1.Field2 = Table2.Field2

and did not select verify; I simply ran the tool and my output table was correctly created.

Another Stackexchange question referencing this error; Review User2118's answer

  • 2
    I ran into the same thing with a FGDB. I ran the tool once with quotes ("table.field" = "table2.field") like the Expression Builder created, and the Verify and the tool failed. I removed the quotes (table.field = table2.field) and the verify failed, but the tool ran successfully. Both ArcGIS Desktop and the FGDB are 10.2.2.
    – Dan
    Commented Sep 23, 2014 at 17:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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