Complex SQL query help

Neven Draskovic

14y2.1kSQL

Hey

I have a somewhat complex problem that I need help with. I'm making an application that generates a report containing times of departure from a certain bus station. These departures are all binded to bus lines, and this is where I get the problem. Since these stations might be somewhere in the middle of the line, I need to display the starting and the final station that this bus is going from/to. The problem is that the names and ID's of the stations are all in the same table in the database, and I use that table to identify the station for which I'm generating the report. I'm trying to solve this by using the same table multiple times (in the FROM section I have Stations AS S1, Stations AS S2), but no luck so far. I know that the explanation is a bit complicated, but so is the problem, so please help, an if you need some more informations, I will try to give them to you.

Thanks

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Neven Draskovic14y ago
I can give you the schema's (only the fields I need for this query) of the tables involved and explain their roles, my query is not giving the proper results. There are 4 tables that I need to use:

Stations - ID, Name

Lines - LineID, LineName

Timetable - TimetableID, LineID, Heading, Regime // Regime defines on which days the bus drives

LinePaths - ID, TimetableID, SN, Arrival, Departure, StationID // The SN is a serial number that defines the order of stations on a certain paths (the bus will arrive to those stations in that particular order).

The result should have the following schema:
FinalStationName,TimeOfDeparture,Regime

With this schema, I can group informations in my report by the FinalStationName, and that would be the final result. The name of the station does not have to be in the result because I can simply pass it as a parameter to the report
0
Kunal Vaishya14y ago
Dear ,

If it possible then give the code or Query so we will help you ?
0