Originally posted by ligang
I do feel those kind of issue should be solved by using some procedure not a single SQL
No. These issues are solved by good db design. I don't like your third table, it will only cause pain in the long run.

Have a look at what happens if you introduce instead an entity called "line segment" which defines the piece of the line between two consecutive stops. (This, I think, breaks up a M-N into a 1-M and a 2-M). Have a look at "connect by prior" to walk along the line, segment by segment.