A challenge SQL
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: A challenge SQL

  1. #1
    Join Date
    Aug 2000
    Location
    Shanghai
    Posts
    433

    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

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    STOP -- LINE has M -- N relation...?

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    I charge 200 CHF per hour for solving problems like this (I'm cheap!)

  4. #4
    Join Date
    Aug 2000
    Location
    Shanghai
    Posts
    433
    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

  5. #5
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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.

  6. #6
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142

    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.

  7. #7
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    (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!

  8. #8
    Join Date
    Aug 2000
    Location
    Shanghai
    Posts
    433
    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

  9. #9
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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);

  10. #10
    Join Date
    Aug 2000
    Location
    Shanghai
    Posts
    433
    excellent explanation !
    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
  •  


Click Here to Expand Forum to Full Width