5 Replies Latest reply: Aug 17, 2012 12:11 PM by Peter vd Zwan RSS

    Need SQL Query

    Karthickumar Pillaiyarsamy
      Can anyone guide me?

      I have a table with the following columns

      Unique key(Stop_Name,Stop_Id)

      Create table Flight_Chart
      (     ID               Number(5)      Primary Key
           , Flight_No          char(6)          Not Null
           , Stop_Name          Char(3)          Not Null
           , Priority          Number(2)     Not Null
           , Stop_Id          Number(2)     Not Null
      );

      Insert into Flight_Chart values(1,'IND728','CHN',1,4);
      Insert into Flight_Chart values(2,'IND728','BNG','2,6);
      Insert into Flight_Chart values(3,'IND728','HYD',3,7);
      Insert into Flight_Chart values(4,'IND728','MUM',4,8);
      Insert into Flight_Chart values(5,'IND728','DEL',5,11);
      Insert into Flight_Chart values(6,'IND163','CHN',1,4);
      Insert into Flight_Chart values(7,'IND163','MUM',2,8);
      Insert into Flight_Chart values(8,'IND163','SNG',3,22);
      Insert into Flight_Chart values(9,'LUF846','MUM',1,8);
      Insert into Flight_Chart values(10,'LUF846','KWT',2,28);
      Insert into Flight_Chart values(11,'LUF846','LND',3,35);
      Insert into Flight_Chart values(12,'LUF846','CND',4,49);
      Insert into Flight_Chart values(13,'CND453','CND',1,49);
      Insert into Flight_Chart values(14,'CND453','LND',2,35);
      Insert into Flight_Chart values(15,'CND453','CHN',3,4);

      select * from flight_chart;


      ID     Flight_No     Stop_Name     Priority     Stop_ID
      1     IND728     CHN     1     4
      2     IND728     BNG     2     6
      3     IND728     HYD     3     7
      4     IND728     MUM     4     8
      5     IND728     DEL     5     11
      6     IND163     CHN     1     4
      7     IND163     MUM     2     8
      8     IND163     SNG     3     22
      9     LUF846     MUM     1     8
      10     LUF846     KWT     2     28
      11     LUF846     LND     3     35
      12     LUF846     CND     4     49
      13     CND453     CND     1     49
      14     CND453     LND     2     35
      15     CND453     CHN     3     4
      Need query for following requirement

      Now i want to search possible way form Chennai(CHN) to London(LND)

      Flight_No     Source     Destination     Stop_Level     Stop_ID
      IND163     CHN     SNG     MUM     8
      IND728     CHN     DEL     MUM     8
      LUF846     MUM     LND     LND     35

      Now i want to search possible way form London(LND) to Canada(CND)

      Flight_No     Source     Destination     Stop_Level     Stop_ID
      LUF846     LND     CND     CND     49

      Thanks in Advance

      Edited by: Karthickumar Pillaiyarsamy on Aug 17, 2012 4:04 PM

      Edited by: Karthickumar Pillaiyarsamy on Aug 17, 2012 7:31 PM
        • 1. Re: Need SQL Query
          Hoek
          Post CREATE TABLE and INSERT INTO statements.

          See #7, 8 and 9 @ {message:id=9360002}
          • 2. Re: Need SQL Query
            Frank Kulash
            Hi,
            Karthickumar Pillaiyarsamy wrote:
            Can anyone guide me?

            I have a table with the following columns

            ID --> Auto-increment
            The is no auto-increment in Oracle
            Flight_No     --> Flight Route Number
            Stop_Name     --> Landing Spot(May be again take up)
            Priority     --> Count of landing
            Explain more about all the columns, especially priority.
            Stop_ID     -->unique stop id
            "Unique" means 2 (or more) rows can't have the same value. It looks like 3 rows have stop_id=4 (to give just one example).
            >
            ID     Flight_No     Stop_Name     Priority     Stop_ID
            1     IND728     CHN     1     4
            2     IND728     BNG     2     6
            3     IND728     HYD     3     7
            4     IND728     MUM     4     8
            5     IND728     DEL     5     11
            6     IND163     CHN     1     4
            7     IND163     MUM     2     8
            8     IND163     SNG     3     22
            9     LUF846     MUM     1     8
            10     LUF846     KWT     2     28
            11     LUF846     LND     3     35
            12     LUF846     CND     4     49
            13     CND453     CND     1     49
            14     CND453     LND     2     35
            15     CND453     CHN     3     4
            Need query for following requirement

            Now i want to search possible way form Chennai(CHN) to London(LND)

            Flight_No     Source     Destination     Stop_Level     Stop_ID
            IND163     CHN     SNG     MUM     8
            IND728     CHN     DEL     MUM     8
            LUF846     MUM     LND     LND     35

            Now i want to search possible way form London(LND) to Canada(CND)
            Is this a completely separate query?
            Flight_No     Source     Destination     Stop_Level     Stop_ID
            LUF846     LND     CND     CND     49

            Thanks in Advance
            The most practical solution would be PL/SQL. Exactly how to do it depends on your exact requirements (in particular what you want when there are multiple ways to get fromn the same starting point to the same destination) and your version of Oracle.

            Something like this might help you:
            SELECT     SYS_CONNECT_BY_PATH (id, '/')     AS path
            FROM     table_x
            WHERE     CONNECT_BY_ISLEAF     = 1
            AND     stop_name          = 'LND'          -- or whatever end point you want
            START WITH     stop_name     = 'CNH'          -- or whatever starting point you want
            CONNECT BY NOCYCLE     (     flight_no     = PRIOR  flight_no
                              AND     priority     > PRIOR  priority
                           )
                 OR          (     flight_no     != PRIOR fight_no
                           AND     stop_name     =  PRIOR stop_name
                           )
            ;
            This requires Oracle 10.1 or higher.
            If you have an auto-increment column, then you're not using Oracle. Perhaps your database as recursive WITH clauses (also available in Oracle starting with version 11.2), which can do the same thing, and might be better for your requirements.

             

            Whenever you post a question, you should incluude CREATE TABLE and INSERT statements for you sample data, and the version of Oracle (or whatver software you're using). Use \
             tags to make your message readable.
            As the previous reply said, you should read the forum FAQ{message:id=9360002}, which explains all this and more.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
            • 3. Re: Need SQL Query
              Karthickumar Pillaiyarsamy
              Hi Frank,


              Sorry. Auto increment means sequence number.

              Priority ---> current stop priority is 1
              next stop priority is 2 ...... until it reaches the destination.

              If CHN to LND

              CHN Priority is 1
              next stop is 2
              nxt stop 3
              next stop 4
              LND priority is 5



              Unique column (Stop_name,Stop_Id)
              • 4. Re: Need SQL Query
                Brian Bontrager
                As defined, I don't see an a way to get what you need. (I used to work in the aviation industry, so I've had to write similar queries).

                Your Priority column appears to give the order of legs (a single row) within a route (a unique flight_no), but I don't see anything that tells us the relationship of legs in different routes.

                IND728 and IND163 both get you from CHN to MUM. If one of them arrives in MUM after LUF846 departs MUM is that still a valid flight to consider to get from CHN to LON?

                Including both the departure and arrival locations on each row will make it possible to find paths between a given start and end point using legs from multiple routes. This will be similar to the START WITH... CONNECT BY Frank posted above.

                Including arrival and departure times (UTC) will make it possible to remove the combinations that are not feasible due to timing, because that gives something to order by that is common across airlines.
                • 5. Re: Need SQL Query
                  Peter vd Zwan
                  Hi,

                  You can solve your question like this:
                  with Flight_Chart  (ID, Flight_No, Stop_Name, Priority, Stop_ID)as
                    (select 1,  'IND728', 'CHN', 1, 4 FROM DUAL UNION ALL
                    select 2,  'IND728', 'BNG', 2, 6 FROM DUAL UNION ALL
                    select 3,  'IND728', 'HYD', 3, 7 FROM DUAL UNION ALL
                    select 4,  'IND728', 'MUM', 4, 8 FROM DUAL UNION ALL
                    select 5,  'IND728', 'DEL', 5, 11 FROM DUAL UNION ALL
                    select 6,  'IND163', 'CHN', 1, 4 FROM DUAL UNION ALL
                    select 7,  'IND163', 'MUM', 2, 8 FROM DUAL UNION ALL
                    select 8,  'IND163', 'SNG', 3, 22 FROM DUAL UNION ALL
                    select 9,  'LUF846', 'MUM', 1, 8 FROM DUAL UNION ALL
                    select 10, 'LUF846', 'KWT', 2, 28 FROM DUAL UNION ALL
                    select 11, 'LUF846', 'LND', 3, 35 FROM DUAL UNION ALL
                    select 12, 'LUF846', 'CND', 4, 49 FROM DUAL UNION ALL
                    select 13, 'CND453', 'CND', 1, 49 FROM DUAL UNION ALL
                    select 14, 'CND453', 'LND', 2, 35 FROM DUAL UNION ALL
                    select 15, 'CND453', 'CHN', 3, 4 FROM DUAL
                    )
                  ,Flight_Chart_A AS
                    (
                    SELECT
                      ID
                      ,FLIGHT_NO
                      ,PRIORITY
                      ,PRIOR STOP_NAME    F_NAME
                      ,STOP_NAME          T_NAME
                      ,PRIOR STOP_ID      F_ID
                      ,STOP_ID            T_ID
                    
                    FROM
                      Flight_Chart 
                    
                    WHERE
                      PRIOR STOP_ID IS NOT NULL
                    
                    START WITH
                      PRIORITY = 1
                  
                    CONNECT BY
                      PRIOR FLIGHT_NO = FLIGHT_NO
                      AND PRIOR PRIORITY + 1 = PRIORITY
                  
                    ORDER BY
                      FLIGHT_NO
                      ,PRIORITY
                    )
                  ,POS_ROUTE AS
                    (
                    SELECT
                      FLIGHT_NO
                      ,F_NAME
                      ,T_NAME
                      ,LEVEL STOP_COUNT
                      ,SYS_CONNECT_BY_PATH(FLIGHT_NO || ',' || PRIORITY, '/') ROUTE
                    
                    FROM
                      Flight_Chart_A 
                    
                    WHERE
                      T_NAME = 'LND'
                    
                    START WITH
                      F_NAME = 'CHN'
                    
                    CONNECT BY NOCYCLE
                      PRIOR T_ID = F_ID
                    )
                  ,FAST_ROUTE AS
                    (
                    SELECT
                      ROUTE
                      ,STOP_COUNT
                    
                    FROM
                      POS_ROUTE
                    
                    WHERE
                      STOP_COUNT = (SELECT MIN(STOP_COUNT) FROM POS_ROUTE)
                      AND ROWNUM = 1
                    )
                  
                  SELECT
                    *
                  
                  FROM
                   Flight_Chart_A
                  
                  WHERE
                    INSTR((SELECT ROUTE FROM FAST_ROUTE), FLIGHT_NO || ',' || PRIORITY ) > 0
                  
                  ;
                  
                  ID FLIGHT_NO PRIORITY F_NAME T_NAME F_ID T_ID
                  -- --------- -------- ------ ------ ---- ----
                   7 IND163           2 CHN    MUM       4    8 
                  10 LUF846           2 MUM    KWT       8   28 
                  11 LUF846           3 KWT    LND      28   35 
                  But I think you should google the Dijkstra algorithem. This solves the "shortest" or "cheapest" route trought a network.
                  This can be solved in SQL with model clause or PL/SQL.

                  Regards,

                  Peter