title: How to create a left outer join to one table using SQLAlchemy
body: <p>I'm struggling to understand how to use left outer join targeting a single table in SQLAlchemy.</p>
  
  <p>I have the following SQL query:</p>
  
  <pre><code>select r1.*, r1.result as result1, r2.result as result2 
  from 
      (
          select * from participation 
          where day_id = 1 
          and sport_id = 1
      )
      r1 
  left join 
      (
          select * from participation
          where day_id = 3
          and sport_id = 1
      )
      r2 
  on r1.participant_id = r2.participant_id
  </code></pre>
  
  <p>Which gives me the following resultset:</p>
  
  <pre><code>-------------------------------------------------------------------
  | participant_id | sport_id | day_id | result | result1 | result2 |
  -------------------------------------------------------------------
  |              1 |        1 |      1 |      7 |       7 |       7 |
  |              2 |        1 |      1 |     13 |      13 |         |
  |              3 |        1 |      1 |      0 |       0 |       1 |
  |              4 |        1 |      1 |      5 |       5 |       5 |
  |              5 |        1 |      1 |     20 |      20 |      21 |
  -------------------------------------------------------------------
  </code></pre>
  
  <p>And I currently have the following SQLAlchemy script:</p>
  
  <pre><code>results = db.session.query(Participation). \
      &lt;maybe something here?&gt;
      all()
  </code></pre>
  
  <p>But that returns each result as its own row. Can anyone point out what I need to make that work?</p>