MySQL Query To find Trains between two stations

Question

My Train Details table,

Table Name- traindetailtb

Id  Train_Number  Train_Name       From   To
1    27658         Venad Express    ABC   XYZ
2    27659         Venad Express    XYZ   ABC

and my Train Days Table,

Table Name- traindaystb
Id  Train_Number Days
1    27658       Sunday
2    27658       Wednesday
3    27659       Saturday
4    27659       Friday

and this my Train Schedule Table,

Table Name- scheduletb
Id  Train_Number  Station  Time
1    27658          ABC     09:00am  
2    27658          CDE     10:00am
3    27658          XYZ     11:00am
4    27659          XYZ     12:00pm
5    27659          CDE     01:00pm
6    27659          ABC     02:00pm

There will be two input, From and To

I need a query that gives all Trains(ie,Train_Number,Train_Name,Days) between the given stations

For eg: For input From- CDE To-XYZ ----> output will be-- 27658 Venad Express S W

and For input From- XYZ To-CDE ----> output will be-- 27659 Venad Express M T

Can anyone Please help me to do this.

Answer 1

Are you looking for something like this?

SELECT q.train_number, MAX(t.train_name) train_name, GROUP_CONCAT(LEFT(d.days, 1)) days
  FROM
(
  SELECT s.train_number
    FROM scheduletb s JOIN scheduletb e
      ON s.train_number = e.train_number
   WHERE s.station = 'CDE' -- from
     AND e.station = 'XYZ' -- to
     AND s.id < e.id
) q JOIN traindaystb d
    ON q.train_number = d.train_number JOIN traindetailtb t
    ON q.train_number = t.train_number
 GROUP BY q.train_number

Output for CDE -> XYZ:

| TRAIN_NUMBER |    TRAIN_NAME | DAYS |
|--------------|---------------|------|
|        27658 | Venad Express |  S,W |

Output for XYZ -> CDE:

| TRAIN_NUMBER |    TRAIN_NAME | DAYS |
|--------------|---------------|------|
|        27659 | Venad Express |  S,F |

Here is SQLFiddle demo