SQL query to fetch data from three tables

Question

I don't know SQL much, so I need help. I have three tables RoomTrains, RoomSchedule, and RoomStations in SQLite. I want to fetch data from these three tables.

RoomTrains

RoomSchedule

RoomStations

I want to fetch all the trains from the train table in this format

data class SearchedTrainInfo(
val trainName: String?=null,
val trainNumber: Int?=null,
val fromStationName: String?=null,
val fromStationCode: String?=null,
val fromSTA: Int?=null,
val fromSTD: Int?=null,
val fromKM: Float?=null,
val fromHaltNo: Int?=null,
val toStationName: String?=null,
val toStationCode: String?=null,
val toSTA: Int?=null,
val toSTD: Int?=null,
val toKM: Float?=null,
val toHaltNo: Int?=null,
val runDays: Int?=null

)

    abstract fun getFilteredAndSortedTrainsDAO(
        fromStationFilter: String?,
        toStationFilter: String?,
    )

When no filter(fromStationFilter is null) is passed it should return all trains with source destination details in the above data class.

When filtered stations are passed, the query should return stopping trains(when stop==1 in the schedule table) between those stations. it should look for filtered stations in the schedule table.

How it would know source and destination stations, from srCode, and destCode from the train table? Another way is when sta = 1 and std!=1 is the source station of that train, and vice versa for the destination station.

Result like this - if no filter passed-

trainName-Pune - Kolhapur SCSMT Special
trainNumber-1023
fromStationName-PUNE JN
fromStationCode- PUNE
fromSTA-1
fromSTD-1305
fromKM-0.0
fromHaltNo-1
toStationName-C SHAHU M RAJ KOLHAPUR TERM
toStationCode-KOP
toSTA-1780
toSTD-1
toKM-326
toHaltNo-22
runDays-127

like this for all trains from train table

When filtered stations are passed

fromStationFilter-JJR
toStationFilter-HTK



trainName-Pune - Kolhapur SCSMT Special
trainNumber-1023
fromStationName-JEJURI
fromStationCode- JJR
fromSTA-1364
fromSTD-1365
fromKM-59
fromHaltNo-3
toStationName-HATKANAGALE
toStationCode-HTK
toSTA-1674
toSTD-1675
toKM-306
toHaltNo-19
runDays-127

like this all the trains between above two stations

Answer 1

AS the question, as asked, does not consider many of the recommendations for asking a question, this answer is a pointer to some of the considerations, it is not intended to be a fully complete answer.

You should read the following:-

• https://stackoverflow.com/help/how-to-ask
  • the question is asking for a full answer to what is quite a complex and multi-faceted issue.
• https://stackoverflow.com/help/minimal-reproducible-example
  • screens shots of data are unacceptable and especially so when data is truncated
  • you should provide a means of easily generating sufficient data to provide an answer, not expect those answering the question to spend their time constructing the data.

The 3 core tables

In regard to providing the data you could have used an SQLite tool to create and populate the 3 tables e.g. (as used for this answer):-

DROP TABLE IF EXISTS roomtrains;
DROP TABLE IF EXISTS roomstations;
DROP TABLE IF EXISTS roomschedule;
CREATE TABLE roomtrains (numb INTEGER PRIMARY KEY, name, type, srcode,destcode,destname,rundays,classes,totaldist,jourtim,deparr,updatedon);
CREATE TABLE roomstations (name, code PRIMARY KEY, lat,lng,stattype,statename,statecode);
CREATE TABLE roomschedule (numb INTEGER, stncode,statno,haltno,stop,sta,std,daynum,km,PRIMARY KEY (numb,stncode));

INSERT INTO roomtrains VALUES
    (1023,'P....',16,'PUNE','PUNE JN','KOP',127,'3A,SL,2A',326.0,475,'1305-1700',240606)
    ,(1024,'K....',16,'KOP','C SHAH','PUNE',127,'3A,SL,2A',326.0,495,'1410-1905',240606)
;
INSERT INTO roomstations VALUES
    ('Kxxxx','KOP',1.23,3.21,1,'State1','S1')
    ,('Pxxxx','PUNE',1.23,3.21,1,'State1','S1')
    ,('SSVxxxx','SSV',1.23,3.21,1,'State1','S1')
;
INSERT INTO roomschedule VALUES
    (1023,'PUNE',1,0,1,1664,1305,1,0.0)
    ,(1023,'KOP',25,22,1,1780,1,2,326.0)
    ,(1023,'SSV',2,1,1,1312,1315,1,10.0)
    ,(1024,'KOP',1,1,1,1664,1305,1,0.0)
    ,(1024,'PUNE',25,22,1,1780,1,2,326.0)
;

• quite obviously the above is very limited data any may be inaccurate and has been tailored as there are obvious omissions e.g. no station in the question with a code of KOP.

Tailoring the output to suit the recipient Object

Room needs to be able to map an extracted value to the respective field. In this case most of the fields in the SearchedTrainInfo match actual column names. This requires either the field name being changed or the respective output column being renamed/named. The latter is afford by using AS to name/rename the output columns.

e.g. roomtrains.name AS trainName renames the column from name to be output as traniName.

How to get data from the 3 tables

The crux is the roomschedule table as this associates the train and a station. So you:-

1. JOIN the train table with the schedule table, then you
2. JOIN the schedule table with the station table

e.g.

FROM roomtrains
/* JOIN the schedules for the train */
JOIN roomschedule ON roomtrains.numb = roomschedule.numb
/* Join the station for the schedule */
JOIN roomstations ON roomschedule.stncode = roomstations.code

• the result being the cartesian product of the trains, with the scheduled stations for the trains and in the intermediate and respective schedule columns.

• using the testing data above then part of the result (not all columns) is:-

  • 
  • here an image of the data is fine as there is no intention of that data being needed to be copied and pasted.

HOWEVER

You say How it would know source and destination stations, from srCode, and destCode from the train table?

• it is assumed that by destination you mean the next leg(halt) of the journey(schedule for the train) Note! whether or not a trains stops has been ignored for brevity and that it is relatively easy to implement by adding the condition

As you want many values from the schedule and station for the next stop/halt then it is probably prudent to add all columns from the schedule (the to station data/columns will follow):-

Determining the schedule could be according to the next halt that is higher then the current halt. So the 2nd JOIN of the schedule table would be according to the next higher halt.

Another complexity is one of identifier (table/column names) ambiguities (above you can see such a potential ambiguity with the name column, adding columns a second time would introduce numerous ambiguities making it impossible to ascertain which identifier is which). As such AS can be applied to a table name to rename that instance/occurrence for the duration of the execution of the query.

As such a third JOIN could be:-

JOIN roomstations ON roomschedule.stncode = roomstations.code
/* Join the schedule again for the next halt according to the next highest haltno
    OR when there is no higher haltno then the current haltno (i.e. the END)
*/
JOIN roomschedule AS nh ON 
    nh.numb = roomschedule.numb 
    AND nh.haltno = 
    coalesce(
        (
            SELECT haltno 
            FROM roomschedule AS nexthalt 
            WHERE nexthalt.haltno > roomschedule.haltno 
            ORDER BY nexthalt.haltno ASC 
            LIMIT 1
        ),
        roomschedule.haltno
    )

• so this 2nd instance of the schedule table is named as nh
• the JOIN is for the same train AND the next haltno. The next haltno is determined by the subquery.
• the result if NULL (the last halt/stop will not have a higher halt/stop) so the coalesece function is used to say that the next haltno is the same haltno (and thus not exclude the row because there is no join/joined schedule).
  • ORDER BY and LIMIT 1 ensures that the single value returned by the sub query is the next haltno.

Your output requirement requires that the station info for the next station is included, so the station table is joined to the nh table, this 2nd occurrence of the station table being named as toStn. The actual join being:-

JOIN roomstations AS tostn ON tostn.code = nh.stncode

So you have the query as:-

SELECT *
FROM roomtrains
    /* JOIN the schedules for the train */
    JOIN roomschedule ON roomtrains.numb = roomschedule.numb
    /* Join the station for the schedule */
    JOIN roomstations ON roomschedule.stncode = roomstations.code
    /* Join the schedule again for the next halt according to the next highest haltno
        OR when there is no higher haltno then the current haltno (i.e. the END)
    */
    JOIN roomschedule AS nh ON 
        nh.numb = roomschedule.numb 
        AND nh.haltno = 
        coalesce(
            (
                SELECT haltno 
                FROM roomschedule AS nexthalt 
                WHERE nexthalt.haltno > roomschedule.haltno 
                ORDER BY nexthalt.haltno ASC 
                LIMIT 1
            ),
            roomschedule.haltno
        )
    /* JOIN the station for the next halt */
    JOIN roomstations AS tostn ON tostn.code = nh.stncode 
;

This would result in:-

numb    name    type    srcode  destcode    destname    rundays classes totaldist   jourtim deparr  updatedon   numb(1) stncode statno  haltno  stop    sta std daynum  km  name(1) code    lat lng stattype    statename   statecode   numb(2) stncode(1)  statno(1)   haltno(1)   stop(1) sta(1)  std(1)  daynum(1)   km(1)   name(2) code(1) lat(1)  lng(1)  stattype(1) statename(1)    statecode(1)
1023    P....   16  PUNE    PUNE JN KOP 127 3A,SL,2A    326.0   475 1305-1700   240606  1023    PUNE    1   0   1   1664    1305    1   0.0 Pxxxx   PUNE    1.23    3.21    1   State1  S1  1023    SSV 2   1   1   1312    1315    1   10.0    SSVxxxx SSV 1.23    3.21    1   State1  S1
1023    P....   16  PUNE    PUNE JN KOP 127 3A,SL,2A    326.0   475 1305-1700   240606  1023    KOP 25  22  1   1780    1   2   326.0   Kxxxx   KOP 1.23    3.21    1   State1  S1  1023    KOP 25  22  1   1780    1   2   326.0   Kxxxx   KOP 1.23    3.21    1   State1  S1
1023    P....   16  PUNE    PUNE JN KOP 127 3A,SL,2A    326.0   475 1305-1700   240606  1023    SSV 2   1   1   1312    1315    1   10.0    SSVxxxx SSV 1.23    3.21    1   State1  S1  1023    KOP 25  22  1   1780    1   2   326.0   Kxxxx   KOP 1.23    3.21    1   State1  S1
1024    K....   16  KOP C SHAH  PUNE    127 3A,SL,2A    326.0   495 1410-1905   240606  1024    KOP 1   1   1   1664    1305    1   0.0 Kxxxx   KOP 1.23    3.21    1   State1  S1  1024    PUNE    25  22  1   1780    1   2   326.0   Pxxxx   PUNE    1.23    3.21    1   State1  S1
1024    K....   16  KOP C SHAH  PUNE    127 3A,SL,2A    326.0   495 1410-1905   240606  1024    PUNE    25  22  1   1780    1   2   326.0   Pxxxx   PUNE    1.23    3.21    1   State1  S1  1024    PUNE    25  22  1   1780    1   2   326.0   Pxxxx   PUNE    1.23    3.21    1   State1  S1

• note that this is simply a cut and paste of the tabbed data with the column names. In short there are a large number of columns many being ambiguous.

Trimming the output to only what is required

The following both restricts the output to the required data and also renames the output columns to suit the SearchStationInfo object (as included in the comments):-

SELECT 
    /* example of specific columns renamed to suit Room mapping output to a SearchedTrainInfo object */
    /* AS per
            data class SearchedTrainInfo(
                val trainName: String?=null,
                val trainNumber: Int?=null,
                val fromStationName: String?=null,
                val fromStationCode: String?=null,
                val fromSTA: Int?=null,
                val fromSTD: Int?=null,
                val fromKM: Float?=null,
                val fromHaltNo: Int?=null,
                val toStationName: String?=null,
                val toStationCode: String?=null,
                val toSTA: Int?=null,
                val toSTD: Int?=null,
                val toKM: Float?=null,
                val toHaltNo: Int?=null,
                val runDays: Int?=null
            )
    */
    roomtrains.name AS trainName, 
    roomtrains.numb AS trainNumber,
    roomstations.name AS fromStation,
    roomstations.code AS fromStationCode,
    roomschedule.sta AS fromSTA,
    roomschedule.std AS fromSTD,
    roomschedule.km AS fromKM,
    roomschedule.haltno AS fromHaltNo,
    tostn.name||CASE tostn.name WHEN roomstations.name THEN '-JRNY END' ELSE '' END AS toStation,
    tostn.code AS toStationCode,
    nh.sta AS toSTA,
    nh.std AS toSTD,
    nh.km AS toKM,
    nh.haltno AS toHaltNo,
    roomtrains.runDays AS runDays
    .... rest of the query 

• Note! the toStationName if it is the last stop/halt (which has been set to be the same as the fromStation) has had -JRNY END appended.

So the output, using the data, is the more manageable and meaningful:-

• again note that the toStationName has JRNY END appended when it is the last station.

The WHERE clause

You indicate to inputs fromStationFilter and toStationFilter and that one condition is to include ALL train/schedule/stations if niether have been provided. This can be achieved by testing the length of both strings when they have been concatenated as a single string using the length function. e.g.

WHERE
/* NO filters (length of both concatenated is 0) then include ALL*/
NOT length(:fromStationFilter||:toStationFilter)

An interpretation of the condition in regards to fromStationFilter and toStationFilter being provided could be:-

OR (
    length(:fromStationFilter||:toStationFilter) 
    AND roomschedule.stop /* must be a stop */
    AND (fromStation LIKE :fromStationFilter OR tostn.name LIKE :toStationFilter)
)

• noting the use of tostn (2nd occurrence of the roomstations table).

At a guess you would want the output ordered according to train and the halt/stano so you would probably want something along the lines of:-

 ORDER BY trainNumber,fromhaltno

• 2nd column could perhaps be stano

Testing

To both test the intermediate stages and the final query (not adapted for Room) for both no filters and with both filters then:-

DROP TABLE IF EXISTS roomtrains;
DROP TABLE IF EXISTS roomstations;
DROP TABLE IF EXISTS roomschedule;
CREATE TABLE roomtrains (numb INTEGER PRIMARY KEY, name, type, srcode,destcode,destname,rundays,classes,totaldist,jourtim,deparr,updatedon);
CREATE TABLE roomstations (name, code PRIMARY KEY, lat,lng,stattype,statename,statecode);
CREATE TABLE roomschedule (numb INTEGER, stncode,statno,haltno,stop,sta,std,daynum,km,PRIMARY KEY (numb,stncode));

INSERT INTO roomtrains VALUES
    (1023,'P....',16,'PUNE','PUNE JN','KOP',127,'3A,SL,2A',326.0,475,'1305-1700',240606)
    ,(1024,'K....',16,'KOP','C SHAH','PUNE',127,'3A,SL,2A',326.0,495,'1410-1905',240606)
;
INSERT INTO roomstations VALUES
    ('Kxxxx','KOP',1.23,3.21,1,'State1','S1')
    ,('Pxxxx','PUNE',1.23,3.21,1,'State1','S1')
    ,('SSVxxxx','SSV',1.23,3.21,1,'State1','S1')
;
INSERT INTO roomschedule VALUES
    (1023,'PUNE',1,0,1,1664,1305,1,0.0)
    ,(1023,'KOP',25,22,1,1780,1,2,326.0)
    ,(1023,'SSV',2,1,1,1312,1315,1,10.0)
    ,(1024,'KOP',1,1,1,1664,1305,1,0.0)
    ,(1024,'PUNE',25,22,1,1780,1,2,326.0)
;
SELECT * 
FROM roomtrains
    /* JOIN the schedules for the train */
    JOIN roomschedule ON roomtrains.numb = roomschedule.numb
    /* Join the station for the schedule */
    JOIN roomstations ON roomschedule.stncode = roomstations.code
;
SELECT *
FROM roomtrains
    /* JOIN the schedules for the train */
    JOIN roomschedule ON roomtrains.numb = roomschedule.numb
    /* Join the station for the schedule */
    JOIN roomstations ON roomschedule.stncode = roomstations.code
    /* Join the schedule again for the next halt according to the next highest haltno
        OR when there is no higher haltno then the current haltno (i.e. the END)
    */
    JOIN roomschedule AS nh ON 
        nh.numb = roomschedule.numb 
        AND nh.haltno = 
        coalesce(
            (
                SELECT haltno 
                FROM roomschedule AS nexthalt 
                WHERE nexthalt.haltno > roomschedule.haltno 
                ORDER BY nexthalt.haltno ASC 
                LIMIT 1
            ),
            roomschedule.haltno
        )
    /* JOIN the station for the next halt */
    JOIN roomstations AS tostn ON tostn.code = nh.stncode 
;
SELECT 
    /* example of specific columns renamed to suit Room mapping output to a SearchedTrainInfo object */
    /* AS per
            data class SearchedTrainInfo(
                val trainName: String?=null,
                val trainNumber: Int?=null,
                val fromStationName: String?=null,
                val fromStationCode: String?=null,
                val fromSTA: Int?=null,
                val fromSTD: Int?=null,
                val fromKM: Float?=null,
                val fromHaltNo: Int?=null,
                val toStationName: String?=null,
                val toStationCode: String?=null,
                val toSTA: Int?=null,
                val toSTD: Int?=null,
                val toKM: Float?=null,
                val toHaltNo: Int?=null,
                val runDays: Int?=null
            )
    */
    roomtrains.name AS trainName, 
    roomtrains.numb AS trainNumber,
    roomstations.name AS fromStation,
    roomstations.code AS fromStationCode,
    roomschedule.sta AS fromSTA,
    roomschedule.std AS fromSTD,
    roomschedule.km AS fromKM,
    roomschedule.haltno AS fromHaltNo,
    tostn.name||CASE tostn.name WHEN roomstations.name THEN '-JRNY END' ELSE '' END AS toStation,
    tostn.code AS toStationCode,
    nh.sta AS toSTA,
    nh.std AS toSTD,
    nh.km AS toKM,
    nh.haltno AS toHaltNo,
    roomtrains.runDays AS runDays
FROM roomtrains
    /* JOIN the schedules for the train */
    JOIN roomschedule ON roomtrains.numb = roomschedule.numb
    /* Join the station for the schedule */
    JOIN roomstations ON roomschedule.stncode = roomstations.code
    /* Join the schedule again for the next halt according to the next highest haltno
        OR when there is no higher haltno then the current haltno (i.e. the END)
    */
    JOIN roomschedule AS nh ON 
        nh.numb = roomschedule.numb 
        AND nh.haltno = 
        coalesce(
            (
                SELECT haltno 
                FROM roomschedule AS nexthalt 
                WHERE nexthalt.haltno > roomschedule.haltno 
                ORDER BY nexthalt.haltno ASC 
                LIMIT 1
            ),
            roomschedule.haltno
        )
    /* JOIN the station for the next halt */
    JOIN roomstations AS tostn ON tostn.code = nh.stncode 
;
SELECT 
    /* example of specific columns renamed to suit Room mapping output to a SearchedTrainInfo object */
    /* AS per
            data class SearchedTrainInfo(
                val trainName: String?=null,
                val trainNumber: Int?=null,
                val fromStationName: String?=null,
                val fromStationCode: String?=null,
                val fromSTA: Int?=null,
                val fromSTD: Int?=null,
                val fromKM: Float?=null,
                val fromHaltNo: Int?=null,
                val toStationName: String?=null,
                val toStationCode: String?=null,
                val toSTA: Int?=null,
                val toSTD: Int?=null,
                val toKM: Float?=null,
                val toHaltNo: Int?=null,
                val runDays: Int?=null
            )
    */
    roomtrains.name AS trainName, 
    roomtrains.numb AS trainNumber,
    roomstations.name AS fromStation,
    roomstations.code AS fromStationCode,
    roomschedule.sta AS fromSTA,
    roomschedule.std AS fromSTD,
    roomschedule.km AS fromKM,
    roomschedule.haltno AS fromHaltNo,
    tostn.name||CASE tostn.name WHEN roomstations.name THEN '-JRNY END' ELSE '' END AS toStation,
    tostn.code AS toStationCode,
    nh.sta AS toSTA,
    nh.std AS toSTD,
    nh.km AS toKM,
    nh.haltno AS toHaltNo,
    roomtrains.runDays AS runDays
FROM roomtrains
    /* JOIN the schedules for the train */
    JOIN roomschedule ON roomtrains.numb = roomschedule.numb
    /* Join the station for the schedule */
    JOIN roomstations ON roomschedule.stncode = roomstations.code
    /* Join the schedule again for the next halt according to the next highest haltno
        OR when there is no higher haltno then the current haltno (i.e. the END)
    */
    JOIN roomschedule AS nh ON 
        nh.numb = roomschedule.numb 
        AND nh.haltno = 
        coalesce(
            (
                SELECT haltno 
                FROM roomschedule AS nexthalt 
                WHERE nexthalt.haltno > roomschedule.haltno 
                ORDER BY nexthalt.haltno ASC 
                LIMIT 1
            ),
            roomschedule.haltno
        )
    /* JOIN the station for the next halt */
    JOIN roomstations AS tostn ON tostn.code = nh.stncode 
WHERE
    /* NO filters (length of both concatenated is 0) then include ALL*/
    NOT length(''/*fromStationFilter*/||''/*toStationFilter*/)
    /* If Filters THEN*/
    OR (
        length(''/*fromStationFilter*/||''/*toStationFilter*/) 
        AND roomschedule.stop 
        AND (fromStation LIKE '' OR tostn.name LIKE '')
    )
ORDER BY trainNumber,fromhaltno
;

SELECT 
    /* example of specific columns renamed to suit Room mapping output to a SearchedTrainInfo object */
    /* AS per
            data class SearchedTrainInfo(
                val trainName: String?=null,
                val trainNumber: Int?=null,
                val fromStationName: String?=null,
                val fromStationCode: String?=null,
                val fromSTA: Int?=null,
                val fromSTD: Int?=null,
                val fromKM: Float?=null,
                val fromHaltNo: Int?=null,
                val toStationName: String?=null,
                val toStationCode: String?=null,
                val toSTA: Int?=null,
                val toSTD: Int?=null,
                val toKM: Float?=null,
                val toHaltNo: Int?=null,
                val runDays: Int?=null
            )
    */
    roomtrains.name AS trainName, 
    roomtrains.numb AS trainNumber,
    roomstations.name AS fromStation,
    roomstations.code AS fromStationCode,
    roomschedule.sta AS fromSTA,
    roomschedule.std AS fromSTD,
    roomschedule.km AS fromKM,
    roomschedule.haltno AS fromHaltNo,
    tostn.name||CASE tostn.name WHEN roomstations.name THEN '-JRNY END' ELSE '' END AS toStation,
    tostn.code AS toStationCode,
    nh.sta AS toSTA,
    nh.std AS toSTD,
    nh.km AS toKM,
    nh.haltno AS toHaltNo,
    roomtrains.runDays AS runDays
FROM roomtrains
    /* JOIN the schedules for the train */
    JOIN roomschedule ON roomtrains.numb = roomschedule.numb
    /* Join the station for the schedule */
    JOIN roomstations ON roomschedule.stncode = roomstations.code
    /* Join the schedule again for the next halt according to the next highest haltno
        OR when there is no higher haltno then the current haltno (i.e. the END)
    */
    JOIN roomschedule AS nh ON 
        nh.numb = roomschedule.numb 
        AND nh.haltno = 
        coalesce(
            (
                SELECT haltno 
                FROM roomschedule AS nexthalt 
                WHERE nexthalt.haltno > roomschedule.haltno 
                ORDER BY nexthalt.haltno ASC 
                LIMIT 1
            ),
            roomschedule.haltno
        )
    /* JOIN the station for the next halt */
    JOIN roomstations AS tostn ON tostn.code = nh.stncode 
WHERE
    /* NO filters (length of both concatenated is 0) then include ALL*/
    NOT length('Pxxxx'/*fromStationFilter*/||'SSVxxxx'/*toStationFilter*/)
    /* If Filters THEN*/
    OR (
        length('Pxxxx'/*fromStationFilter*/||'SSVxxxx'/*toStationFilter*/) 
        /*AND roomschedule.stop */
        AND (fromStation LIKE 'Pxxxx' OR fromStation LIKE 'SSVxxxx')
    )
ORDER BY trainNumber,roomschedule.statno /* could be haltno */
;

DROP TABLE IF EXISTS roomtrains;
DROP TABLE IF EXISTS roomstations;
DROP TABLE IF EXISTS roomschedule;

The intermediate results are as above, the 4th output with no filters but ordered:-

The output with station Pxxxx as the from station and SSVxxxx as the to station results in:-