title: select on results of a select
body: <p>I have a database table that records movements of users in a game. Each time they move I record their user id, the move_index (increments each row) and the id of the zone they are in. I have a special move_index value of -1 to indicate the last movement of that user.</p>
  
  <pre><code>id   user_id  move_index  zone_id   
  ----------------------------------------------------------------
  0    11        0          0  
  1    11        1          0 
  2    11        2          0   
  3    11       -1          3    
  
  4    22        0          0   
  5    22        1          1   
  6    22        2          1    
  7    22       -1          3    
  </code></pre>
  
  <p>I want to do two things with sql:</p>
  
  <ul>
  <li>Detect all the users who started and finished in specific zones (e.g. started in zone 0 and finished in zone 3)</li>
  <li>Extending the above, detect all users who started and finished in specific zones AND passed through a specific zone.</li>
  </ul>
  
  <p>I know how to do this with multiple SQL statements &amp; java - but I don't know how to do this in a single SQL statement. Do I need to do a select and then a select on the results of this select ?</p>