-
A challenge SQL
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 .
1. www.dbasupport.com
2. www.dbforums.com
3. www.itpub.net
4. www.csdn.net
5. www.umlchina.com
6. www.tek-tips.com
7. www.cnforyou.com
8. fm365.federal.com.cn
9. www.programmersheaven.com
10.http://msdn.microsoft.com/library/default.asp
ligang1000@hotmail.com
-
STOP -- LINE has M -- N relation...?
-
I charge 200 CHF per hour for solving problems like this (I'm cheap!)
-
it is M-N relation .
I do feel those kind of issue should be solved by using some procedure not a single SQL
1. www.dbasupport.com
2. www.dbforums.com
3. www.itpub.net
4. www.csdn.net
5. www.umlchina.com
6. www.tek-tips.com
7. www.cnforyou.com
8. fm365.federal.com.cn
9. www.programmersheaven.com
10.http://msdn.microsoft.com/library/default.asp
ligang1000@hotmail.com
-
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.
-
Re: A challenge SQL
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.
-
(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!
-
appreciate very much if you could provide some example data
1. www.dbasupport.com
2. www.dbforums.com
3. www.itpub.net
4. www.csdn.net
5. www.umlchina.com
6. www.tek-tips.com
7. www.cnforyou.com
8. fm365.federal.com.cn
9. www.programmersheaven.com
10.http://msdn.microsoft.com/library/default.asp
ligang1000@hotmail.com
-
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:
Code:
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);
-
1. www.dbasupport.com
2. www.dbforums.com
3. www.itpub.net
4. www.csdn.net
5. www.umlchina.com
6. www.tek-tips.com
7. www.cnforyou.com
8. fm365.federal.com.cn
9. www.programmersheaven.com
10.http://msdn.microsoft.com/library/default.asp
ligang1000@hotmail.com
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|