According to my notes, I first used the Make Query Table tool in my first week at Aurecon, back in March 2012. It was the first of many, many times, because often when receiving spatial data in a non-spatial format from a non-GIS user, the first thing that gets thrown out is any trace of the original spatial component.
At some point, I realised the tool’s expression parameter was a bit wonky. As I have come up against this problem every few months since (forgetting it happens each time because I only thought to write down a note about it now), I have decided to immortalise it in a gist below.
When inputting the optional SQL clause, ArcGIS automatically adds quotation marks “” to the field names in the dialog box. This will
pass the tool’s error checking successfully but will cause the tool to fail with an error.
If you verify the SQL clause in the dialog box, it will give a SQL error with no specifics. When adding the clause, remember to remove
the quotation marks.
e.g. If you want to join Layer1 to Layer 2 on common field ID and where Layer 1 contains “Cape Town”, ArcGIS will format your expression
in the following way:
"Layer1.ID" = "Layer2.ID" AND "Layer1.TOWN" = 'Cape Town'
You need to change it to
Layer1.ID = Layer2.ID AND Layer1.TOWN = 'Cape Town'