Joining multiple geodatabase tables via inner join

Question

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.

Thanks

Lee

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

LeeStamm · Answer

Hi there,

just in case anyones interested, the solution to the problem is as follows
i had several tables (table 1 to 3) which i wanted to combine into a combined table as per attached worksheet by joining on Roadname , a common field.

the solution- using arcpy.MakeQueryTable_management is as follows. This works no worries and ill use this for simple joins in future.

arcpy.MakeQueryTable_management("aspect;elevation;rainfall","QueryTable","USE_KEY_FIELDS","#","aspect.Roadname #;aspect.Aspect #;elevation.Elevation #;rainfall.Rainfall #","rainfall.Roadname = elevation.Roadname and rainfall.Roadname = aspect.Roadname")

However, when i tried to join 7 tables each of about 14000 rows each, it took a long time to display the table and convert it to a table. - probably because of the memory usage of joining the 7 tables.

cheers

Example for arcgis.xlsx

LeeStamm · Answer

Thanks Guys for your suggestions,

i worked around it by doing a JoinField_management (in_data, in_field, join_table, join_field, {fields})
 several times. that worked fine.

ive also explored the method mzcoyle suggested. that could work too.

however the neatest way would still be via the make query table management tool if i could work that out. i may try again at a later date.

cheers
Leestamm

MathewCoyle · Answer

Thanks,

but my question was really about how to specify the table join. ive tried putting the tables in the input parameter, but it doesnt make sense anyway as you need to tell arcpy how they are related.

ive also tried to put sql in the expression parameter but i dont think i got it right.

ideally id be able to find an example of where this is already done.

Thanks anyway.

I would make a table view of the fields you want joined for each table then use add join for each of them, allowing you to specify an inner join. You can then copy out the table view with the joins to a permanent table. This should restrict the output to only the fields specified for each table.