I am attempting to make a left join on two tables: invoices and vendors. The typical problem is that I have multiple entries in the right table (vendors) which leads to 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 Two Alphas 003 3.75
004 Apple Cartz 003 10
004 23
004 56
004 80
I'm looking for this:
Desired Result:
vend_id line_amt name
001 5.95 Lowes
002 17 Bills
002 25 Bills
002 40 Bills
003 4.35 Two Alphas
003 3.75 Two Alphas
003 10 Two Alphas
004 23 Apple Cartz
004 56 Apple Cartz
004 80 Apple Cartz
But I am 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 Two Alphas
003 3.75 Two Alphas
003 10 Two Alphas
004 23 Apple Cartz
004 56 Apple Cartz
004 80 Apple Cartz
So I'm trying the code below to join on a selectable in sqlalchemy core, but I am getting a Not an executable clause error. I can't use the ORM because of the way the db is set up. Is there a way to alter this code or a better solution that I am not thinking of?
conn = engine.connect()
a = 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 the invoices table to the aliased table will work. I needed to use .select_from in order to complete the join. This is the code that works:
conn = engine.connect()
a = 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)