Trouble with SQL Statment

Question

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.

The first query returns the IDs of trains that are not full.

select TrainID 
  from Train 
 where Capacity > 0;

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.

The second query returns the RouteID of a train given a destination and origin:

select * 
  from Timetable 
 where RouteID = (select RouteID 
                    from Routes 
                   where OriginID = "New York" 
                     and DestinationID = "LA");

The Question/Need

I want to merge these two queries together so I can have something like:

Give me all the Timetable entries for this route ONLY IF there's room on the train.

I'm not a DB kinda guy so I'm just having trouble putting two and two together.

Edit: Schema is outlined below. Thanks for the answers so far, I'm going to try them now.

Routes

+---------------+-------------+------+-----+---------+-------+
| 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    |       |
+---------------+-------------+------+-----+---------+-------+

Stations

+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| StationID | varchar(25) | NO   |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+

Timetable

+-------------+-------------+------+-----+---------------------+----------------+
| 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 |                |
+-------------+-------------+------+-----+---------------------+----------------+

Train

]+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| TrainID  | varchar(11) | NO   | PRI | NULL    |       |
| Capacity | int(11)     | NO   |     | 50      |       |
+----------+-------------+------+-----+---------+-------+

Answer 1

Assuming there is a trainid in the timetable table this should be close

select a.*
from Timetable a
inner join Train b on a.trainid = b.trainid and b.capacity > 0
where a.RouteID =
     (
     select RouteID
     from Routes
     where OriginID = "New York" and DestinationID="LA"
     );

Answer 2

You have not stated how TrainID relates to Route or Timetable.. There needs to be a relationship of some sort to perform this task.

If it is a standard relationship then...

select t.* 
from Timetable t, Routes r, Trains tr
where r.OriginID ='New York' 
and r.DestinationID ="LA"
and t.RouteID = r.RouteID
and tr.RouteID = t.RouteID
amd tr.Capacity > 0;

Regards, Chris

Answer 3

Guessing your schema slightly:

SELECT  t.*
FROM    Timetable t
        INNER JOIN
                Routes r
                ON t.RouteID = r.RouteID
        INNER JOIN
                Trains tr
                ON t.TrainID = tr.TrainID
WHERE   r.OriginID = 'New York'
AND     r.DestinationID = 'LA'
AND     tr.Capacity > 0

Joining to Routes rather than using a subquery could well perform better.

Answer 4

Select * from Timetable ti 
inner join Train tr 
    ON ti.TrainID = tr.ID 
    (Or whatever link there is between the two tables)
WHERE ti.RouteID in 
    (select RouteID from Routes where OriginID="New York" 
    and DestinationID="LA") 
AND tr.Capacity > 0

Answer 5

Im going to assume that the TrainID is stored in the Routes table, this isnt clear in the question.

select * from Timetable where RouteID in 
(select RouteID from Routes 
 inner join Train on Train.TrainID = Routes.TrainID
where 
  OriginID = "New York" 
  and DestinationID = "LA"
  and Capacity > 0;
);