Joining multiple geodatabase tables via inner join

Discussion created by leestamm on Jan 17, 2013
Latest reply on Feb 13, 2013 by leestamm
Hi there,

could some one point me in the right direction on how to join multiple geodatabase tables via inner join.

i have 6 tables in my geodatabase. each shares a common key field, ill call it roadname and it is a text field.
each table has a different attribute about that road stored in another field in that table.

i want to join all six tables together via an inner join, keeping the roadname field from the first table, and specifying which (one) of the fields in each of the other tables i want to have in the new table. (this is a piece of cake in sql, but im new to python)

i can do it with two tables - called rain and geol, as below in the extract of python script

arcpy.MakeQueryTable_management (["rain","geol"], "queryout","ADD_VIRTUAL_KEY_FIELD", "",
                   [["rain.roadname", 'Roadname'],["rain.mean_grid_code", 'Rainfall'],["geol.descriptio", 'geology']],
                   "rain.roadname = geol.roadname ")

but i cant see how to make an expression that joins all six tables at once on road name and selects the field desired from each of the tables. ive tried believe me.



PS. normally id do it in model builder and export to script, but i cant get it to work there.
PPS. Arcgis 10.1