Professional Web Applications Themes

help with this query - MySQL

I am completely clueless about the following query. - - Where does the longest flight go? This relates to 2 tables and the sql scripts are as follows: CREATE TABLE `my-database`.`flight_profiles` ( `FLP_ID` decimal(8,0) default NULL, `FLT_NUMB` decimal(4,0) default NULL, `PRICE_CODE` varchar(50) default NULL, `FLT_FROM` varchar(50) default NULL, `FLT_TO` varchar(50) default NULL, `depart` datetime default NULL, `arrive` datetime default NULL ) ENGINE=InnoDB DEFAULT CHT=latin1; CREATE TABLE `my-database`.`cities` ( `IATA_CODE` varchar(3) default NULL, `COU_IATA_CODE` varchar(2) default NULL, `NAME` varchar(40) default NULL ) ENGINE=InnoDB DEFAULT CHT=latin1; Would appreciate help with this one. Also I am just building my SQL and MySQL skills ...

  1. #1

    Default help with this query

    I am completely clueless about the following query.

    - - Where does the longest flight go?

    This relates to 2 tables and the sql scripts are as follows:

    CREATE TABLE `my-database`.`flight_profiles` (
    `FLP_ID` decimal(8,0) default NULL,
    `FLT_NUMB` decimal(4,0) default NULL,
    `PRICE_CODE` varchar(50) default NULL,
    `FLT_FROM` varchar(50) default NULL,
    `FLT_TO` varchar(50) default NULL,
    `depart` datetime default NULL,
    `arrive` datetime default NULL
    ) ENGINE=InnoDB DEFAULT CHT=latin1;


    CREATE TABLE `my-database`.`cities` (
    `IATA_CODE` varchar(3) default NULL,
    `COU_IATA_CODE` varchar(2) default NULL,
    `NAME` varchar(40) default NULL
    ) ENGINE=InnoDB DEFAULT CHT=latin1;

    Would appreciate help with this one. Also I am just building my SQL
    and MySQL skills so any recommendations on resources like books or
    online resources would be most welcome.

    Thanks

    ros

    ros Guest

  2. #2

    Default Re: help with this query

    ros wrote: 
    That depends on where it is leaving from. From where I am I think it is
    Australia.
     
    The tables are very interesting, but what do you want us to help with?



    Paul Guest

  3. #3

    Default Re: help with this query

    On Apr 14, 10:05 pm, "Paul Lautman" <com>
    wrote: 

    >
    > That depends on where it is leaving from. From where I am I think it is
    > Australia.
    >
    >
    >
    >






    >
    > The tables are very interesting, but what do you want us to help with?[/ref]


    :-)

    isn't this a clue?

    `depart` datetime default NULL,
    `arrive` datetime default NULL

    strawberry Guest

  4. #4

    Default Re: help with this query

    On Apr 14, 3:58 pm, "strawberry" <com> wrote: [/ref]
    > [/ref]

    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]

    >
    > :-)
    >
    > isn't this a clue?
    >
    > `depart` datetime default NULL,
    > `arrive` datetime default NULL[/ref]

    That was hilarious!
    Actually I need help in writing this query which gives me the name of
    the city where the longest flight go from these 2 tables.
    Would be thankful for guidance.
    ros

    ros Guest

  5. #5

    Default Re: help with this query

    On Apr 15, 9:15 am, "ros" <com> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]



    >
    > That was hilarious!
    > Actually I need help in writing this query which gives me the name of
    > the city where the longest flight go from these 2 tables.
    > Would be thankful for guidance.
    > ros[/ref]

    Note that you may need to take account of timezones. Also I don't
    understand the relationship between the cities table and the
    flight_profiles table, so I've ignored the cities table for the
    purposes of this query:

    SELECT FLP_ID, FLT_FROM, FLT_TO, TIMESTAMPDIFF(
    SECOND , depart, arrive ) duration
    FROM flight_profiles
    ORDER BY duration DESC;

    strawberry Guest

  6. #6

    Default Re: help with this query

    On Apr 15, 3:02 am, "strawberry" <com> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]

    >
    > Note that you may need to take account of timezones. Also I don't
    > understand the relationship between the cities table and the
    > flight_profiles table, so I've ignored the cities table for the
    > purposes of this query:
    >
    > SELECT FLP_ID, FLT_FROM, FLT_TO, TIMESTAMPDIFF(
    > SECOND , depart, arrive ) duration
    > FROM flight_profiles
    > ORDER BY duration DESC;[/ref]

    Thanks a lot Strawberry. That was extremely helpful.
    I really appreciate that.
    ros

    ros Guest

  7. #7

    Default Re: help with this query

    On 14 Apr, 23:58, "strawberry" <com> wrote: [/ref]
    > [/ref]

    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]

    >
    > :-)
    >
    > isn't this a clue?
    >
    > `depart` datetime default NULL,
    > `arrive` datetime default NULL- Hide quoted text -
    >
    > - Show quoted text -[/ref]

    I was thinking of "longest" as in distance!

    Captain Guest

  8. #8

    Default Re: help with this query

    On Apr 16, 10:34 am, "Captain Paralytic" <com>
    wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]




    >
    > I was thinking of "longest" as in distance![/ref]

    I know

    strawberry Guest

  9. #9

    Default Re: help with this query

    On Apr 16, 2:54 am, "strawberry" <com> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]

    >
    > I know[/ref]

    Sorry about the confusion but actually it was the duration!

    ros Guest

Similar Threads

  1. Query of Queries on query New type query
    By david_h in forum Coldfusion Database Access
    Replies: 2
    Last Post: May 6th, 08:55 PM
  2. Replies: 1
    Last Post: July 2nd, 09:09 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139