Joining in SQLite to get desired records

I am working on a mobile application which takes two inputs – Source Station Name and Destination Station Names. Upon receiving these two inputs, the application would then enlist the names of trains available for the given stations along with their source_arrival and destination_reach timings.

(Note: For now, I am only focusing on the unreserved local trains that operate in the state of West Bengal, India)

I am using SQLite as the RDBMS. I have following three tables as the sources of the data –

train_table (which has the details of the trains available):
station_table (which contains the details of the stations):
route_table (which contains the route details):

Now, my aim is to produce the output in the following manner as specified earlier (suppose I gave Baruipur Jn as source and Sealdah as destination):

I am unable to figure out the query needed for this. Initially, I was trying something like the following:

    select r1.trainId, r1.arrival as SrcArrive, r2.arrival 
    as Reach  from route_table r1 cross join route_table r2 
    where r1.trainId = r2.trainId and r1.stationId <> r2.stationId and 
    r1.arrival <> r2.arrival;

(Yes, without the trainName)

But I was unable to cut down the unintended source_arrival timings. However, I was able to retrieve the number of different trains available for given two stations with the following:

    select _id, trainNO, trainName from train_table where _id in 
    (select trainId from route_table where stationId = 109 
    INTERSECT 
    select trainId from route_table where stationId = 21);

But with this, I am not able to get to the final result that I need.

Answer

This might work, try once.

select routeData.*, train_table.* from (select r1.trainId, r1.arrival as SrcArrive, r2.arrival 
as Reach  from route_table r1 cross join route_table r2 
where r1.trainId = r2.trainId and r1.stationId <> r2.stationId and 
r1.arrival <> r2.arrival) routeData inner join train_table on routeData.trainId=train_table._id;

I have redifined the selection from route table, try this updated one:

select trainName, SrcArrival, Destination from (select trainData.trainName, route.* from 
(select A.trainId, A.arrival as SrcArrival, B.trainId, B.arrival as Destination from 
route_table A inner join route_table B on A.trainId=B.trainId where A.stationId=109 and 
B.stationId=259 and A.arrival<B.arrival) route inner join train_table trainData on 
route.trainId=trainData._id) order by SrcArrival, Destination;

Advertisement

Answer