title: Nested SELECT using sqlalchemy
body: <p>I would like to create a query with nested SELECT using sqlalchemy, but I cannot get the expected result.</p>
  
  <p>I of course simplified the following query so it will be easilly understandable for this post.</p>
  
  <p>Here is the query I would like to construct:</p>
  
  <pre><code>SELECT pear_table.size,
         (SELECT MIN(apple.apple_date)
          FROM apple
          WHERE apple_id = pear_table.pear_id ) apple_min,
         (SELECT max(lemon.lemon_date)
          FROM lemon
          WHERE lemon_id = pear_table.pear_id ) lemon_max
  FROM
    (SELECT pear_id
     FROM pear
     WHERE pear_color = 'green') pear_table
  </code></pre>
  
  <p>Notice that I use 'pear_id' in both of my subqueries. This query works well when used as a string.</p>
  
  <p>Now I try to construct it using sqlalchemy:</p>
  
  <pre><code>APPLE = wrapper.getMapper('apple')
  LEMON = wrapper.getMapper('lemon')
  PEAR = wrapper.getMapper('pear')
  
  pear_table = select([PEAR.apple_id])
  pear_table.append_whereclause(PEAR.pear_color == 'green')
  
  apple_min = select([func.min(APPLE.apple_date).label('apple_min')])
  apple_min.append_whereclause(APPLE.apple_id == pear_table.pear_id)
  
  lemon_max = select([func.min(LEMON.apple_date).label('lemon_max')])
  lemon_max.append_whereclause(LEMON.lemon_id == pear_table.pear_id)
  
  main_query = select([pear_table.c.pear_id,
                       apple_min.c.apple_min,
                       lemon_max.c.lemon_max])
  </code></pre>
  
  <p>And here is what sqlalchemy construct with this code:</p>
  
  <pre><code>SELECT pear_table.size,
         apple_min,
         lemon_max
  FROM
    (SELECT pear_id
     FROM pear
     WHERE pear_color = 'green') pear_table,
    (SELECT MIN(apple.apple_date)
     FROM apple
     WHERE apple_id = pear_table.pear_id ) apple_min,
    (SELECT max(lemon.lemon_date)
     FROM lemon
     WHERE lemon_id = pear_table.pear_id ) lemon_max
  </code></pre>
  
  <p>The problem is that 'pear_id' is unreachable by my 2 subqueries 'apple' and 'lemon', because sqlalchemy put the subqueries in the 'FROM' clause.</p>
  
  <p>I tried to fix my problem using the correlate option:</p>
  
  <pre><code>.
  apple_min = select([func.min(APPLE.apple_date).label('apple_min')]).correlate(None)
  .
  lemon_max = select([func.min(LEMON.apple_date).label('lemon_max')]).correlate(None)
  .
  </code></pre>
  
  <p>Here is what I get:</p>
  
  <pre><code>SELECT pear_table.size,
         apple_min,
         lemon_max
  FROM
    (SELECT pear_id
     FROM pear
     WHERE pear_color = 'green') pear_table,
    (SELECT MIN(apple.apple_date)
     FROM apple,
          (SELECT pear_id
           FROM pear
           WHERE pear_color = 'green')
     WHERE apple_id = pear_table.pear_id ) apple_min,
    (SELECT max(lemon.lemon_date)
     FROM lemon,
          (SELECT pear_id
           FROM pear
           WHERE pear_color = 'green')
     WHERE lemon_id = pear_table.pear_id ) lemon_max
  </code></pre>
  
  <p>This duplicates the 'FROM pear_id ...', and I don't want duplicate queries for obvious performance reasons.</p>
  
  <ul>
  <li>Am I doing something wrong?</li>
  <li>Is there a better way to construct my query?</li>
  <li>Is sqlalchemy allow me to construct the way I want?</li>
  <li>Is there a way to put the subqueries directly in the main 'SELECT' clause?</li>
  </ul>
  
  <p>I am currently using sqlalchemy 0.4, but I tried the same thing with 0.8 and the result is the same.</p>