Results 1 to 2 of 2

Thread: Query / Table creation advice

  1. #1

    Thread Starter
    PowerPoster make me rain's Avatar
    Join Date
    Sep 2008
    Location
    india/Hubli
    Posts
    2,208

    Query / Table creation advice

    I have a table which stores the Station names and their incremental distance, i can able to calculate the inter distance between the two stations some thing like the ( Distance Of station xxx - Distance Of station yyy) .

    But Some stations are junction stations at where another route diverges / Begins from the main route, i am having issue in such cases how to calculate the inter distance and also query the details of between stations using VIA (i mean junction station)

    my table structure so far i planned is like this
    Code:
    CREATE TABLE `RouteTest` (`StationName`  varchar(10) NOT NULL ,
    `StationGeographicalOrderIndex`  smallint(3) NOT NULL ,
    `IncrementalDistance`  smallint(2) NOT NULL ,
    `Is_Junction`  enum('Y','N') NOT NULL DEFAULT 'N' ,
    `Is_NewDeviatingRouteBegin`  enum('Y','N') NOT NULL DEFAULT 'N' ,
    `DeviatingJunctionStation`  varchar(255) NULL
     COMMENT 'If the station is the beginning of a new route from a junction then from which station it is deviating' 
    )
    ;
    Now i will insert some imaginary data for demo
    Code:
    INSERT INTO routetest VALUES('A',0,0,'Y','N',NULL) ,
    ('B',1,7,'N','N',NULL) ,
    ('C',2,17,'Y','N',NULL) ,
    ('D',3,28,'N','N',NULL) ,
    ('E',4,45,'N','N',NULL)
    Now here P is a station deviating from junction station C
    Code:
    INSERT INTO routetest VALUES('P',0,0,'N','Y','C') ,
    ('Q',1,9,'N','N',NULL) ,
    ('R',2,17,'N','N',NULL) ,
    ('S',3,22,'N','N',NULL) ,
    ('T',4,37,'Y','N',NULL)
    So i must be able to
    (1) Get the details of the stations Starting from Station A and till T Via (that is DeviatingJunctionStation) Station C
    (2) and the Inter distance between A and C
    (3) Thus the user can able to query the stations between any two stations using the Via junction or without

    How to materialize this please
    The averted nuclear war
    My notes:

    PrOtect your PC. MSDN Functions .OOP LINUX forum
    .LINQ LINQ videous
    If some one helps you please rate them with out fail , forum doesn't expects any thing other than this

  2. #2
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Amsterdam...
    Posts
    5,343

    Re: Query / Table creation advice

    I'd set up a table of routes to use the table of stations and connections you have already.

    Alternatively you'd need to bring back every single route possible by connecting the stations then filter those results to the stations you are interested in...

    Have a look at recursive loops.

    If you are coding, either on the sql server or other package you may be able to use source/start station as a starting point, loop all connected and recursivce loop until either they have no other connections or you are at the destination station

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

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