Query a related table and export it with joined feature attributes?

[Anonymous User]

I have a related table I'm querying based on a date field but I want to include the attributes from the feature it is related to. Any ideas?

[RandyBurton · Accepted solution]

I would suggest reading the tables into a couple of dictionaries, using a process similar to what is described in Richard Fairhurst‌'s blog: Turbo Charging Data Manipulation with Python Cursors and Dictionaries.

One way would be:

import arcpy

relatedTbl = r"C:\Path\to\file.gdb\InspectionHistory"
relatedFields = ['OBJECTID', 'IDENTIFIER', 'ESTABLISHMENTNAME', 'CITY', 'INSPDATE'] # IDENTIFIER is the key field and second in field list
relatedWhere = "INSPDATE BETWEEN DATE '2017-09-01' AND DATE'2017-10-01'"

# dictionary in format {'ITEMID': ('IDENTIFIER', 'ITEM1', 'ITEM2', ...), ...}
relatedDict = {r[0]:(r[1:]) for r in arcpy.da.SearchCursor(relatedTbl, relatedFields, where_clause=relatedWhere)}

# print relatedDict

# get a list of keys for parent where clause: v[0] = IDENTIFIER
keyList = []
for k, v in relatedDict.iteritems():
    # print k, v[0]
    keyList.append(v[0])


parentFC = r"C:\Path\to\file.gdb\RestaurantInspections"
parentFields = ['IDENTIFIER', 'OBJECTID', 'ESTABLISHMENTNAME', 'CITY'] # IDENTIFIER is the key field and is first in field list

parentWhere = "IDENTIFIER IN ('{}')".format("','".join(set(keyList))) # assuming IDENTIFIER is string, using ' -- omit for numeric key
print parentWhere

# dictionary in format {'IDENTIFIER': ('ITEM1', 'ITEM2', ...), ...}
parentDict = {r[0]:(r[1:]) for r in arcpy.da.SearchCursor(parentFC, parentFields, where_clause=parentWhere)}

# print combined results
for k, v in relatedDict.iteritems():
    print k, v[0], v[1], v[2], v[3], parentDict[v[0]][0], parentDict[v[0]][1], parentDict[v[0]][2]‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍