Thanks, that is much more concise than what I came up with. For some reason I wasn't able to specify the columns as orig.c.latitude. I had to use orig.c.state_latlon_latitude. Here is what I ended up with:
state_latlon_joined = FIPS_States.join( state_latlon, onclause=state_latlon.c.state == FIPS_States.c["Alpha code"]) orig = state_latlon_joined.alias("orig") dest = state_latlon_joined.alias("dest") stmt = (select([transactions, orig.c.FIPS_States_Name.label("orig_name"), orig.c["FIPS_States_Alpha code"].label("orig_alpha_code"), dest.c["FIPS_States_Name"].label("dest_name"), dest.c["FIPS_States_Alpha code"].label("dest_alpha_code"), orig.c.state_latlon_latitude.label("orig_lat"), orig.c.state_latlon_longitude.label("orig_lon"), dest.c.state_latlon_latitude.label("dest_lat"), dest.c.state_latlon_longitude.label("dest_lon")]) .select_from(transactions .join(orig, transactions.c.ORIG_STATE == orig.c["FIPS_States_Numeric code"]) .join(dest, transactions.c.DEST_STATE == dest.c["FIPS_States_Numeric code"])) .where(orig.c["FIPS_States_Alpha code"] == bindparam("orig_code")) .where(dest.c["FIPS_States_Alpha code"] == bindparam("dest_code"))) > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.