title: SQLAlchemy convert outer join ORM query to Core
body: <p><strike>I'm having problems with SQLAlchemy's <a href="http://docs.sqlalchemy.org/en/rel_0_8/core/expression_api.html#sqlalchemy.sql.expression.Select.select_from" rel="nofollow"><code>select_from</code></a> statement when using the core component. I try to construct an outer join query which currently looks like:</p>
  
  <pre><code>query = select([b1.c.id, b1.c.num, n1.c.name, n1.c.num, ...]
          ).where(and_(
              ... some conditions ...
          )
      ).select_from(
          ???.outerjoin(
          n1,
          and_(
              ... some conditions ...
          )
      ).select_from(... more outer joins similar to the above ...)
  </code></pre>
  
  <p>According to the docs, the structure should look like this:</p>
  
  <pre><code>table1 = table('t1', column('a'))
  table2 = table('t2', column('b'))
  s = select([table1.c.a]).\
      select_from(
          table1.join(table2, table1.c.a==table2.c.b)
      )
  </code></pre>
  
  <p>My problem is that I don't have a table1 object in this case, as the <code>select ...</code> part consists of columns and not a single table (see question marks in my query). I've tried using  <code>n1.outerjoin(n1...</code>, but that caused an exception (<code>Exception: (ProgrammingError) table name "n1" specified more than once</code>).</p>
  
  <p>The above snippet is derived from a working session-based (ORM) query, which I try to convert (with limited success).</p>
  
  <pre><code>b = Table('b', metadata, 
      Column('id', Integer, Sequence('seq_b_id')),
      Column('num', Integer, nullable=False),
      Column('active', Boolean, default=False),
  )
  n = Table('n', metadata, 
      Column('b_id', Integer, nullable=False),
      Column('num', Integer, nullable=False),
      Column('active', Boolean, default=False),
  )
  p = Table('p', metadata, 
      Column('b_id', Integer, nullable=False),
      Column('num', Integer, nullable=False),
      Column('active', Boolean, default=False),
  )
  
  n1 = aliased(n, name='n1')
  n2 = aliased(n, name='n2')
  b1 = aliased(b, name='b1')
  b2 = aliased(b, name='b2')
  p1 = aliased(p, name='p1')
  p2 = aliased(p, name='p2')
  
  result = sess.query(b1.id, b1.num, n1.c.name, n1.c.num, p1.par, p1.num).filter(
          b1.active==False,
          b1.num==sess.query(func.max(b2.num)).filter(
              b2.id==b1.id
          )
      ).outerjoin(
          n1, 
          and_(
              n1.c.b_id==b1.id,
              n1.c.num&lt;=num,
              n1.c.active==False,
              n1.c.num==sess.query(func.max(n2.num)).filter(
                  n2.id==n1.c.id
              )
          )
      ).outerjoin(
          p1,
          and_(
              p1.b_id==b1.id,
              p1.num&lt;=num,
              p1.active==False,
              p1.num==sess.query(func.max(p2.num)).filter(
                  p2.id==p1.id
              )
          )
      ).order_by(b1.id)
  </code></pre>
  
  <p>How do I go about converting this ORM query into a plain Core query?
  </strike>
  <strong>Update:</strong></p>
  
  <p>I was able to narrow down the problem. It seems that a combination of two <code>select_from</code> calls causes the problem.</p>
  
  <pre><code>customer = Table('customer', metadata,
      Column('id', Integer),
      Column('name', String(50)),
  )
  order = Table('order', metadata,
      Column('id', Integer),
      Column('customer_id', Integer),
      Column('order_num', Integer),
  )
  address = Table('address', metadata,
      Column('id', Integer),
      Column('customer_id', Integer),        
      Column('city', String(50)),
  )
  metadata.create_all(db)
  
  customer1 = aliased(customer, name='customer1')
  order1 = aliased(order, name='order1')
  address1 = aliased(address, name='address1')
  
  columns = [
      customer1.c.id, customer.c.name,
      order1.c.id, order1.c.order_num, 
      address1.c.id, address1.c.city
  ]
  query = select(columns)
  query = query.select_from(
      customer1.outerjoin(
          order1,
          and_(
              order1.c.customer_id==customer1.c.id,
          )
      )
  )
  query = query.select_from(
      customer1.outerjoin(
          address1,
          and_(
              customer1.c.id==address1.c.customer_id
          )
      )
  )    
  result = connection.execute(query)
  for r in result.fetchall():
      print r
  </code></pre>
  
  <p>The above code causes the following exception:</p>
  
  <pre><code>ProgrammingError: (ProgrammingError) table name "customer1" specified more than once
   'SELECT customer1.id, customer.name, order1.id, order1.order_num, address1.id, address1.city \nFROM customer, customer AS customer1 LEFT OUTER JOIN "order" AS order1 ON order1.customer_id = customer1.id, customer AS customer1 LEFT OUTER JOIN address AS address1 ON customer1.id = address1.customer_id' {}
  </code></pre>
  
  <p>If I was a bit more experienced in using SQLAlchemy, I would say this could be a bug...</p>