A challenge SQL

[ligang]

for the transportation system .

A Master Table stores the Stop Name as PK (Stop Master)
Another Master Table stores the Line Name as PK ( Line Master)
The Third Master Table stores the The Relation of Stop and Line
(Line_Detail) . PK is Line Name , Stop Name , Sequence of the Stop in this line .
For Example , StopA , StopB , StopC forms Line1

The Data looks like

Line1 StopA 1 (first stop)
Line1 StopB 2 (second stop)
Line1 StopC 3 (third stop)

now after implementing all the data in the third tables .
how to write a query to fetch all the possible routine from
one Stop to another Stop ?

It is really headache .

[pando]

STOP -- LINE has M -- N relation...?

[DaPi]

I charge 200 CHF per hour for solving problems like this (I'm cheap!)

[ligang]

it is M-N relation .

I do feel those kind of issue should be solved by using some procedure not a single SQL

[DaPi]

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.

[DaPi]

Originally posted by ligang The Third Master Table stores the The Relation of Stop and Line (Line_Detail) . PK is Line Name , Stop Name , Sequence of the Stop in this line .

NOOOOOOOO! The sequence is part of the PK! So you change PK of lots of the stops in the line if you decide to suppress or add a stop ! ! ! I hope you didn't mean that.

[DaPi]

(Good problem, I'm enjoying it - you can give the 200CHF/hr to charity).

I think you can be sure of a good solution when you can model the Circle Line of the London Underground (no first or last stop or any sequence number). Not sure I'm there yet . . . "line-segment" will work but I'm not sure I can avoid infinite loops in the SQL!

[ligang]

appreciate very much if you could provide some example data

[DaPi]

Originally posted by ligang
appreciate very much if you could provide some example data

You're missing out on the fun! If you insist, try the following - I do NOT suggest you create you tables like this, it's just for proof of principle:

create table line(
lineKey   integer,
lineName  varchar2(20)
);

create table line_seg(
lineKey  integer,
fromStop varchar2(1),
toStop   varchar2(1)
);

insert into line values(1,'First');
insert into line values(2,'Second');
insert into line values(3,'Circle');

insert into line_seg values(1,'A','B');
insert into line_seg values(1,'B','C');
insert into line_seg values(1,'C','D');
insert into line_seg values(1,'D','E');
insert into line_seg values(1,'E','F');

insert into line_seg values(2,'B','C');
insert into line_seg values(2,'C','E');

insert into line_seg values(3,'A','B');
insert into line_seg values(3,'B','C');
insert into line_seg values(3,'C','D');
insert into line_seg values(3,'D','A');


select lineName from line l
where 'D' IN
(select s.toStop from line_seg s
 connect by prior s.toStop = s.fromStop
 and              s.lineKey = l.linekey
 and              s.toStop <> 'B'
 start with s.fromStop = 'B'
 and        s.lineKey = l.lineKey);

[ligang]

excellent explanation !