title: Trouble with SQL Statment
body: <p>I have a database for a Train company. I want to combine two queries together so I can use them as one from a Java front end.</p>
  
  <p>The first query returns the IDs of trains that are not full.</p>
  
  <pre><code>select TrainID 
    from Train 
   where Capacity &gt; 0;
  </code></pre>
  
  <p>Each train has a capacity of 50 and I subtract one each time a seat is booked, so if the capacity is greater than zero, then there's a seat on board.</p>
  
  <p>The second query returns the RouteID of a train given a destination and origin:</p>
  
  <pre><code>select * 
    from Timetable 
   where RouteID = (select RouteID 
                      from Routes 
                     where OriginID = "New York" 
                       and DestinationID = "LA");
  </code></pre>
  
  <h2>The Question/Need</h2>
  
  <p>I want to merge these two queries together so I can have something like:</p>
  
  <pre><code>Give me all the Timetable entries for this route ONLY IF there's room on the train.
  </code></pre>
  
  <p>I'm not a DB kinda guy so I'm just having trouble putting two and two together.</p>
  
  <p>Edit: Schema is outlined below. Thanks for the answers so far, I'm going to try them now.</p>
  
  <p>Routes</p>
  
  <pre><code>+---------------+-------------+------+-----+---------+-------+
  | Field         | Type        | Null | Key | Default | Extra |
  +---------------+-------------+------+-----+---------+-------+
  | RouteID       | varchar(25) | NO   | PRI | NULL    |       |
  | OriginID      | varchar(25) | NO   | MUL | NULL    |       |
  | DestinationID | varchar(25) | NO   | MUL | NULL    |       |
  | Duration      | int(3)      | NO   |     | NULL    |       |
  +---------------+-------------+------+-----+---------+-------+
  </code></pre>
  
  <p>Stations</p>
  
  <pre><code>+-----------+-------------+------+-----+---------+-------+
  | Field     | Type        | Null | Key | Default | Extra |
  +-----------+-------------+------+-----+---------+-------+
  | StationID | varchar(25) | NO   |     | NULL    |       |
  +-----------+-------------+------+-----+---------+-------+
  </code></pre>
  
  <p>Timetable</p>
  
  <pre><code>+-------------+-------------+------+-----+---------------------+----------------+
  | Field       | Type        | Null | Key | Default             | Extra          |
  +-------------+-------------+------+-----+---------------------+----------------+
  | TimeID      | int(11)     | NO   | PRI | NULL                | auto_increment |
  | RouteID     | varchar(11) | NO   |     | NULL                |                |
  | TrainID     | varchar(11) | NO   |     | NULL                |                |
  | DepartDate  | timestamp   | NO   |     | 0000-00-00 00:00:00 |                |
  | ArrivalDate | timestamp   | NO   |     | 0000-00-00 00:00:00 |                |
  +-------------+-------------+------+-----+---------------------+----------------+
  </code></pre>
  
  <p>Train</p>
  
  <pre><code>]+----------+-------------+------+-----+---------+-------+
  | Field    | Type        | Null | Key | Default | Extra |
  +----------+-------------+------+-----+---------+-------+
  | TrainID  | varchar(11) | NO   | PRI | NULL    |       |
  | Capacity | int(11)     | NO   |     | 50      |       |
  +----------+-------------+------+-----+---------+-------+
  </code></pre>