I'm new to ArcGIS and was wondering if someone could help me with my SQL expression. Essentially, I'm using ModelBuilder to iterate over several tables and Make Query table for each one. I'm trying to join my tables to shapefiles.
Normally what I would do as far as the SQL expression is: column_name_of_table = column_name_of_my_shapefile

This would work just fine if I use the Make Query table one at a time for each table.
However, I'm having an issue with the SQL expression when I try to iterate over multiple tables.

The column name that I'm using to join to the shapefile colmun name are exactly the same, however the name of the Table itself is not.
For example, I have 4 tables labeled:


They all share the same field name for the field that I want to join to the shapefile.
Each field name is named Locality.
That means that the actual field name is
Out2.Locality and
The shapefile's field name always stays the same,
So when I try to write the SQL expression and iterate it
Out0.Locality = MSA.Locality
will not work for Out1.Locality = MSA.Locality or so on.
How could I fix it so that the SQL expression is consistent regardless of the table name?

  • Have you done this manually yet (outside of modelbuilder) I suggest you start simple and get that working, then build complexity such as joins and outside joins. – Brad Nesom Jul 14 '15 at 20:06
  • Yes I have, it works if I do it one table at a time. For instance, outside of model builder I would use the Make Query tool to join my Out0 table to my MSA shapefile (the SQL expression would be Out0.Locality = MSA.Locality) I'm running into issues when I try to iterate the Make Query tool and use the same SQL expression – moya Jul 14 '15 at 20:09
  • Given that Out0, Out1,Out2 and Out3 are practically the same with the exception of 0,1,2,3 is there anyway that I can write the SQL expression so that it reads Out#.Locality = MSA.Locality? – moya Jul 14 '15 at 20:24
  • 1
    please do not write new question in the comments. Also it is best to ask one question. making each question separate. Please see the help faq here... gis.stackexchange.com/help/how-to-ask – Brad Nesom Jul 14 '15 at 20:39
  • 1
    How many Out#.Locality tables do you have? Will these tables be overwritten or updated before running the model? Reason I ask is if not for my two questions, it may be easier to just manually create a new join field for all the Out#.Locality tables and do a field calculate on them to move over the values. This will allow the join to be predictable (not dynamic). – artwork21 Jul 24 '16 at 15:19