Select statement help.

[ineedalotohelp]

I have 2 tables,

1 called journeys, which contains the journeyID, the start station, end station, time to complete, and the distance. The second table is called connections and contains all the stations the train stops at along the journey. I need to write a query which extracts the correct journey based on 2 connection points/stationID's...

Anyone have any idea how to do this?

I want journey ID based on station ID's in connection point table where both connections are present in the connection point for that journey.

[Arsench]

hi, as I understand (may be not correctly) you need something like this

select table1.journeyID ..... from table1 where table1.journeyID IN (select table2.stationID from table2)

that will select all records from table1 (in this case journeys )where journeyID will be = to table2 (in this case connections) stationID.

If you need more help ask me :)
regards

[ineedalotohelp]

hi, as I understand (may be not correctly) you need something like this

select table1.journeyID ..... from table1 where table1.journeyID IN (select table2.stationID from table2)

that will select all records from table1 (in this case journeys )where journeyID will be = to table2 (in this case connections) stationID.

If you need more help ask me :)
regards

Thanks for getting back to me. Im not sure that will do it, my fault for a poor explanation.

Here is some example data;

table1 = journey

id, startstation, endstation, distance.
1, 1, 5, 800.
2, 2, 6, 400.

this following table contains all of the connecting points, or stations, along the journey or route (e.g. journeyid1 stops at stations 1-5.

table2 = connections
connID, journeyID, StationID.
1, 1, 1.
2, 1, 2.
3, 1, 3.
4, 1, 4.
5, 1, 5.
6, 2, 2.
6, 2, 6.

following table contains station data.

table3 = stations

id, name, address.
1, Euro-Lille, 09939
2, Gare du Paris, 84929
3, Lyon, 24802
4, Dijon, 94024
5, Marseille, 37800.

the query i need requires the Journey id, for journeys which connect at both the stations the traveller has specified.

eg. the traveller wishes to go from 2, paris to 4, dijon, which journey connects at both of these stations?

[Arsench]

Thanks for getting back to me. Im not sure that will do it, my fault for a poor explanation.

Here is some example data;

table1 = journey

id, startstation, endstation, distance.
1, 1, 5, 800.
2, 2, 6, 400.

this following table contains all of the connecting points, or stations, along the journey or route (e.g. journeyid1 stops at stations 1-5.

table2 = connections
connID, journeyID, StationID.
1, 1, 1.
2, 1, 2.
3, 1, 3.
4, 1, 4.
5, 1, 5.
6, 2, 2.
6, 2, 6.

following table contains station data.

table3 = stations

id, name, address.
1, Euro-Lille, 09939
2, Gare du Paris, 84929
3, Lyon, 24802
4, Dijon, 94024
5, Marseille, 37800.

the query i need requires the Journey id, for journeys which connect at both the stations the traveller has specified.

eg. the traveller wishes to go from 2, paris to 4, dijon, which journey connects at both of these stations?

Hello,
Let’s to do it as I understand, if the traveler wants to go from Paris (2) to Dijon (4) so we can do the connection like this:

Select * from journey where startstation IN (select id from stations) AND endstation IN (select id from stations) ORDER BY DESC;

So this will select only the stations which exist in table journey start station and end stations columns. May be you don’t need the table connections.

I think that’s all, is something I’ve understood wrong, please try to ask the question with more details.

[ineedalotohelp]

The connections table is required.

Start station and end station are the stations which the journey begins and ends. In the example of paris to dijon, no journeys begin or end at both of these stations.

I really don't see how i could provide more information that i already have... I might just have to do this programmatically rather than via SQL. Shame, but nevermind. Will get the job done.