design database table for train

 You have given two trains

1) Train A - Mumbai - Pune - Goa - Delhi

2) Train B - Mumbai - Goa - Pune - delhi

design table for this and find train between two stations

create Table Train(

id int primary key auto_increment,

trainName varchar(100)

)

create table Station(

id int primary key auto_increment,

stationName varchar(100)

)

create Table TrainRoute(

id int auto_increment,

trainId int ,

stationId int,

stopIndex int,

primary key (id),

foreign key(trainId) references Train(id),

foreign key(stationId) references Station(id)

)

INSERT INTO Train(trainName) values('TrainA');

INSERT INTO Train(trainName) values('TrainB');

INSERT INTO Station(stationName) values('Mumbai');

INSERT INTO Station(stationName) values('Pune');

INSERT INTO Station(stationName) values('Goa');

INSERT INTO Station(stationName) values('Delhi');

insert into TrainRoute(trainId,stationId,stopIndex) values(1,1,1);

insert into TrainRoute(trainId,stationId,stopIndex) values(1,2,2);

insert into TrainRoute(trainId,stationId,stopIndex) values(1,3,3);

insert into TrainRoute(trainId,stationId,stopIndex) values(1,4,4);

insert into TrainRoute(trainId,stationId,stopIndex) values(2,1,1);

insert into TrainRoute(trainId,stationId,stopIndex) values(2,3,2);

insert into TrainRoute(trainId,stationId,stopIndex) values(2,2,3);

insert into TrainRoute(trainId,stationId,stopIndex) values(2,4,4);

-- FROM Goa to Pune

select A.trainId,C.trainName,(A.stopIndex - B.stopIndex) as stationInBetween from (

select * from TrainRoute where stationId = (select id from Station where stationName='Pune')

) A

inner join (

select * from TrainRoute where stationId = (select id from Station where stationName='Goa')

) B on A.trainId = B.trainId and A.stopIndex > B.stopIndex

inner join Train C on A.trainId = C.id

-- FROM Goa to Delhi

select A.trainId,C.trainName,(A.stopIndex - B.stopIndex) as stationInBetween from (

select * from TrainRoute where stationId = (select id from Station where stationName='Delhi')

) A

inner join (

select * from TrainRoute where stationId = (select id from Station where stationName='Goa')

) B on A.trainId = B.trainId and A.stopIndex > B.stopIndex

inner join Train C on A.trainId = C.id

-- mumbai to pune

select A.trainId,C.trainName,(A.stopIndex - B.stopIndex) as stationInBetween from (

select * from TrainRoute where stationId = (select id from Station where stationName='Pune')

) A

inner join (

select * from TrainRoute where stationId = (select id from Station where stationName='Mumbai')

) B on A.trainId = B.trainId and A.stopIndex > B.stopIndex

inner join Train C on A.trainId = C.id

-- Delhi to Mumbai

select A.trainId,C.trainName,(A.stopIndex - B.stopIndex) as stationInBetween from (

select * from TrainRoute where stationId = (select id from Station where stationName='Mumbai')

) A

inner join (

select * from TrainRoute where stationId = (select id from Station where stationName='Delhi')

) B on A.trainId = B.trainId and A.stopIndex > B.stopIndex

inner join Train C on A.trainId = C.id

-- FROM Goa to Delhi

select C.trainName,(A.stopIndex - B.stopIndex) as stationInBetween from

TrainRoute A

inner join TrainRoute B on A.trainId = B.trainId and A.stopIndex > B.stopIndex

inner join Train C on A.trainId = C.id

where A.stationId =(select id from Station where stationName='Delhi')

AND B.stationId = (select id from Station where stationName='Goa')