python - Joining an aliased selectable in Sqlalchemy Core -

i attempting make left join on 2 tables: invoices , vendors. typical problem have multiple entries in right table (vendors) leads duplicate results:

    vendors             invoices         vend_id name        vend_id  line_amt      001    lowes       001      5.95      001    lowes       002      17      001    lowes_ca    002      25      002    bills       002      40      002    bill's      003      4.35      003    2 alphas  003      3.75      004    apple cartz 003      10                         004      23                         004      56                         004      80 

i'm looking this:

desired result:      vend_id   line_amt      name 001       5.95          lowes 002       17            bills 002       25            bills 002       40            bills 003       4.35          2 alphas 003       3.75          2 alphas 003       10            2 alphas 004       23            apple cartz 004       56            apple cartz 004       80            apple cartz 

but getting this:

vend_id   line_amt      name 001       5.95          lowes 001       5.95          lowes 001       5.95          lowes_ca 002       17            bills 002       17            bill's 002       25            bills 002       25            bill's 002       40            bills 002       40            bill's 003       4.35          2 alphas 003       3.75          2 alphas 003       10            2 alphas 004       23            apple cartz 004       56            apple cartz 004       80            apple cartz 

so i'm trying code below join on selectable in sqlalchemy core, getting not executable clause error. can't use orm because of way db set up. there way alter code or better solution not thinking of?

conn = engine.connect()  = select([vendors.c.vend_id.label('vend_id'), func.min(vendors.c.name).label('name')]).group_by(vendors.c.vend_id).alias('a')  s = select([         invoices.c.vend_id.label('vendor'),         invoices.c.line_amt.label('amount'),         ]).join(a, a.c.vend_id == invoices.c.vend_id)  p = conn.execute(s) 

first joining invoices table aliased table work. needed use .select_from in order complete join. code works:

    conn = engine.connect()      = select([vendors.c.vend_id.label('vend_id'),     func.min(vendors.c.name).label('name')]).group_by(vendors.c.vend_id).alias('a')      j = invoices.join(a, a.c.vend_id == invoices.c.vend_id)      s = select([             invoices.c.vend_id.label('vendor'),             a.c.name.label('name'),             invoices.c.line_amt.label('amount'),             ]).select_from(j)      p = conn.execute(s)