[GIS] Creating join based on multiple fields using ArcGIS Desktop

Question

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.

Answer 1

Your syntax is incorrect.

Reference

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:

"MY_FIELD"

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

[MY_FIELD]

etc...

That would imply the correct format as:

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

Script

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/\")

Result