You could create a query within a query too.
Ok, I tried to create a subquery within a query but that doesn't do it, because the sub-query that I created needs to be join by itself repeatedly.
For example, I created a query:
Code:
SELECT DISTINCT bookingLeg.bookinglegID, bookingLeg.tid, station.stationName AS station1, lineStation.distance_miles AS distance1, dateadd("n", lineStation.time_mins, trains.depart) AS time1, station_1.stationName AS station2, lineStation_1.distance_miles AS distance2, dateadd("n", lineStation_1.time_mins, trains.depart) AS time2, lineStation_1.distance_miles - lineStation.distance_miles AS Diff
FROM trains INNER JOIN (lineStation AS lineStation_1 INNER JOIN (lineStation INNER JOIN ((bookingLeg INNER JOIN station ON bookingLeg.startID = station.stationID) INNER JOIN station AS station_1 ON station_1.stationID = bookingLeg.endID) ON linestation.stationID = station.stationID) ON lineStation_1.stationID = station_1.stationID) ON trains.trainID = bookingleg.tid
WHERE lineStation_1.distance_miles - lineStation.distance_miles >=0;
I called this query nyTOboston, and then I want to join this query to itself but I can't do it in one query so I saved this query, then
I created another query that would joing nyTOboston.bookingLegID to nyTOboston1.bookingLegID and because of this, there is going to be
a many to many relationship because nyTOboston (a query) doesn't have primary and foreign key.