Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
reidacus
Starting Member
2 Posts |
Posted - 2013-01-28 : 17:42:08
|
Hi AllI have a problem where I have a train network and using SQL I need to determine a route through the network, specifying a start and end station. The query that I write should be able to automatically be able to determine which stations the route needs to traverse to reach the destination. I have produced a couple of scaled down tables to start with but I am unsure how to go about creating such a query to return the information that I require. I am familiar with the mathematical works of Dijkstra's algorithm but from what I've researched so far this can be a bit of a nightmare to implement. Is there a simpler way of devising a different solution for this, such as a series of select statements perhaps? Ideally I am looking for an output which lists the hops within the network and the cumulative distance between them to the destination. Apologies if I have lost anyone, I am happy to clarify anything above. The tables I have are outlined below. Place TablePlaceID, ID : (A, 1; B, 2; C, 3; D, 4; E, 5)Route Links TableStart, End, Distance : (A, B, 5; A, C, 27; A, D, 20; B, C, 10; B, E, 21; C, E, 5; D, A, 5; D, E, 6)Any help at all will be much appreciated! |
|
sqlbay
Starting Member
12 Posts |
Posted - 2013-01-29 : 00:58:03
|
Would you please give an example for output?SQL Server Professional http://sqlbay.blogspot.in |
|
|
reidacus
Starting Member
2 Posts |
Posted - 2013-01-29 : 03:01:18
|
quote: Originally posted by sqlbay Would you please give an example for output?
The output would ideally be a table that gave me the from node, the next hop node and the cumulative distance from start to the present node. So if i wanted to calculate a route from A to E using the values above, the output would be similar to below.Eg [From, To, Cumulative_Distance][A, B, 5][B, C, 15][C, E, 20] |
|
|
|
|
|
|
|