Am trying to write a query that will find out a bus route from Place A to Place B even if place A and B are not directly connected by a single route.

Tables
Bus Stop (E.g. Trafalgar Square, Conventry, Park Street)
Route Number (E.g. A1, A2, 45, 345)


Rules:
1 Bus Stop can have 1 or many bus routes.
1 Route will have 1 or many stops

Table 3:
BusStop_RouteNumber


So, if route A1 had both Traf Sq and Coventry, then a query such as
Select number from RouteNumber, BS_RN where RouteNumber.number=BS_RN.number and BS_RN.BusStop = "Coventry" and BS_RN.BusStop = "Traf square"
correct? or no?

Problem if, what if One wants to go from Traf Square to Park Street and there is no direct route?
Say, A1 has Coventry and Traf Square on its route and A2 has Coventry and Park Street on its route.

How would this query be?
Should one run more than 1 query one back to back and then work out a difference?

SELECT DISTINCT Table1.Route_No
FROM BusStop_RouteNumber AS Table1
INNER JOIN BusStop_RouteNumber AS Table2
ON Table1.Route_No = Table2.Route_No
WHERE Table1.Bus_Stop = 'From Stop'
AND Table2.Bus_Stop = 'To Stop'

This should work.

If you want most direct bus route you might want to change table design.

I would rather have only one table...

Bus_Route
Route_No
Bus_Stop_Name
Bus_Stop_No (1, 2, 3... etc to determine which no this bus stop is on this route)

Example records will look like :

A1, Convent Garden, 1
A1, Leceister Square, 2
82, North Finchley, 1
82, Tally Ho Corner, 2
....
82, Convent Garden, 12
....
82, Leceister Square, 15


Now if you want to find shortest route from 'Convent Garden' To 'Leceister Square' Following query will work

SELECT Route_No
FROM Bus_Route AS TableFrom
INNER JOIN Bus_Route AS TableTo
ON TableFrom.Route_No = TableTo.Route_No
WHERE TableFrom.Bus_Stop_Name = 'Convent Garden'
AND TableTo.Bus_Stop_Name = 'Leceister Square'
ORDER BY SQR((TableTo.Bus_Stop_No - TableFrom.Bus_Stop_No) * (TableTo.Bus_Stop_No - TableFrom.Bus_Stop_No))

Note:
1. (TableTo.Bus_Stop_No - TableFrom.Bus_Stop_No) might give you negative no

2. SQR = square root function ... I am doing square of the (TableTo.Bus_Stop_No - TableFrom.Bus_Stop_No)
and then finding the square root to make it positive no... you can use other functions depending on database you are using





Hope this all will help..

hey, thanks for the input,
but i dont think the main part of my query is answered in your response.

When there is a direct route, i.e. both starting and destination are on a particular route, then we can find that out,
BUT
what if the starting and destination stops are not connected by any route directly?

say, one has to get down midway at stop X and catch a bus route 4 that touches the destination but starts somewhere obscure?

To go deeper, sometimes, one may not have even an indirect connection at all, but may need to walk 10 steps from 1 bus stop to another to catch a connector. How do we figure that out?

Then, there is a case where there may be a connector, but one has to go around the world for that, where you could get down at a particular stop and walk 10 steps to another stop to get a connector.

I am preparing the full problem statement and DB and grabbing available information from the Bus Agency's website and populatiing the database.

once all is done, feel free to grab the data from a not-yet-decided location and trying your luck on it. it's one interesting project. atleast for me.
give me 3-4 days atleast.
thanks again.

okay... i think, it might help if you define hotspots (major bus stops)

when there is no direct route then try look for nearest hotspot and have best possible route for hotspots already defined in the database. I think that's how http://www.tfl.gov.uk works..

You can also use Deductive Database for this if you are interested in Artificial intelligence.. just a thought.

Good Luck with your projecct. it sounds really interesting .. !