I am developing a railway reservation system, i am having one issue which i hope could be solved here, my query as below works like this :
there are 2 train; train1 which goes from station 1 to 5 and train2 which goes from station 5 to 10, now when a user selects a ORIGIN location suppose 1 to DESTINATION 4 the result is correct it show's only the train1 and even when i select station 1 to destination station 10 it shows me both trains meaning connecting train thats fine.. but the problem is that if i select from station 1 to station 5 it still shows me both the trains or even if i select from station 5 to station 10 and so on.. it shows me both trains... the result should be only one train ok here is the query:
SELECT DISTINCT tr.trainid, tr.trname
FROM train_journey INNER JOIN
tr ON train_journey.trainid = tr.trainid AND train_journey.trainid = tr.trainid
WHERE (train_journey.stationid = @st1) OR
(train_journey.stationid = @st2) AND EXISTS
(SELECT trainid, stationid
FROM train_journey AS train_journey_1
WHERE (tr.trainid = trainid) AND (stationid = @st2))