Joining an aliased selectable in Sqlalchemy Core
<p>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:</p>

<pre><code>    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
</code></pre>

<p>I'm looking for this:</p>

<pre><code>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
</code></pre>

<p>But I am getting this:</p>

<pre><code>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
</code></pre>

<p>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?</p>

<pre><code>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)
</code></pre>

<p>First joining the invoices table to the aliased table will work. I needed to use <code>.select_from</code> in order to complete the join. This is the code that works:</p>

<pre>    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)
</pre>

python join sqlalchemy core