help with this query
This is a discussion on help with this query within the MySQL Database forums, part of the Database Forums category; I am completely clueless about the following query. - - Where does the longest flight go? This relates to 2 tables and ...
|
|||||||
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
04-14-2007
|
|||
|
|||
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 CHARSET=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 CHARSET=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 
|
|
04-14-2007
|
|||
|
|||
|
Re: help with this query
ros wrote:
> I am completely clueless about the following query. > > - - Where does the longest flight go? That depends on where it is leaving from. From where I am I think it is Australia. > > 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 CHARSET=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 CHARSET=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 The tables are very interesting, but what do you want us to help with?
|
|
04-14-2007
|
|||
|
|||
|
Re: help with this query
On Apr 14, 10:05 pm, "Paul Lautman" <paul.laut...@btinternet.com>
wrote: > ros wrote: > > I am completely clueless about the following query. > > > - - Where does the longest flight go? > > That depends on where it is leaving from. From where I am I think it is > Australia. > > > > > > > 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 CHARSET=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 CHARSET=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 > > The tables are very interesting, but what do you want us to help with? :-) isn't this a clue? `depart` datetime default NULL, `arrive` datetime default NULL
|
|
04-15-2007
|
|||
|
|||
|
Re: help with this query
On Apr 14, 3:58 pm, "strawberry" <zac.ca...@gmail.com> wrote:
> On Apr 14, 10:05 pm, "Paul Lautman" <paul.laut...@btinternet.com> > wrote: > > > > > ros wrote: > > > I am completely clueless about the following query. > > > > - - Where does the longest flight go? > > > That depends on where it is leaving from. From where I am I think it is > > Australia. > > > > 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 CHARSET=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 CHARSET=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 > > > The tables are very interesting, but what do you want us to help with? > > :-) > > isn't this a clue? > > `depart` datetime default NULL, > `arrive` datetime default NULL 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
|
|
04-15-2007
|
|||
|
|||
|
Re: help with this query
On Apr 15, 9:15 am, "ros" <ros...@gmail.com> wrote:
> On Apr 14, 3:58 pm, "strawberry" <zac.ca...@gmail.com> wrote: > > > > > On Apr 14, 10:05 pm, "Paul Lautman" <paul.laut...@btinternet.com> > > wrote: > > > > ros wrote: > > > > I am completely clueless about the following query. > > > > > - - Where does the longest flight go? > > > > That depends on where it is leaving from. From where I am I think it is > > > Australia. > > > > > 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 CHARSET=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 CHARSET=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 > > > > The tables are very interesting, but what do you want us to help with? > > > :-) > > > isn't this a clue? > > > `depart` datetime default NULL, > > `arrive` datetime default NULL > > 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 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;
|
|
04-16-2007
|
|||
|
|||
|
Re: help with this query
On Apr 15, 3:02 am, "strawberry" <zac.ca...@gmail.com> wrote:
> On Apr 15, 9:15 am, "ros" <ros...@gmail.com> wrote: > > > > > On Apr 14, 3:58 pm, "strawberry" <zac.ca...@gmail.com> wrote: > > > > On Apr 14, 10:05 pm, "Paul Lautman" <paul.laut...@btinternet.com> > > > wrote: > > > > > ros wrote: > > > > > I am completely clueless about the following query. > > > > > > - - Where does the longest flight go? > > > > > That depends on where it is leaving from. From where I am I think it is > > > > Australia. > > > > > > 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 CHARSET=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 CHARSET=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 > > > > > The tables are very interesting, but what do you want us to help with? > > > > :-) > > > > isn't this a clue? > > > > `depart` datetime default NULL, > > > `arrive` datetime default NULL > > > 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 > > 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; Thanks a lot Strawberry. That was extremely helpful. I really appreciate that. ros
|
|
04-16-2007
|
|||
|
|||
|
Re: help with this query
On 14 Apr, 23:58, "strawberry" <zac.ca...@gmail.com> wrote:
> On Apr 14, 10:05 pm, "Paul Lautman" <paul.laut...@btinternet.com> > wrote: > > > > > > > ros wrote: > > > I am completely clueless about the following query. > > > > - - Where does the longest flight go? > > > That depends on where it is leaving from. From where I am I think it is > > Australia. > > > > 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 CHARSET=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 CHARSET=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 > > > The tables are very interesting, but what do you want us to help with? > > :-) > > isn't this a clue? > > `depart` datetime default NULL, > `arrive` datetime default NULL- Hide quoted text - > > - Show quoted text - I was thinking of "longest" as in distance!
|
|
04-16-2007
|
|||
|
|||
|
Re: help with this query
On Apr 16, 10:34 am, "Captain Paralytic" <paul_laut...@yahoo.com>
wrote: > On 14 Apr, 23:58, "strawberry" <zac.ca...@gmail.com> wrote: > > > > > On Apr 14, 10:05 pm, "Paul Lautman" <paul.laut...@btinternet.com> > > wrote: > > > > ros wrote: > > > > I am completely clueless about the following query. > > > > > - - Where does the longest flight go? > > > > That depends on where it is leaving from. From where I am I think it is > > > Australia. > > > > > 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 CHARSET=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 CHARSET=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 > > > > The tables are very interesting, but what do you want us to help with? > > > :-) > > > isn't this a clue? > > > `depart` datetime default NULL, > > `arrive` datetime default NULL- Hide quoted text - > > > - Show quoted text - > > I was thinking of "longest" as in distance! I know
|
|
04-16-2007
|
|||
|
|||
|
Re: help with this query
On Apr 16, 2:54 am, "strawberry" <zac.ca...@gmail.com> wrote:
> On Apr 16, 10:34 am, "Captain Paralytic" <paul_laut...@yahoo.com> > wrote: > > > > > On 14 Apr, 23:58, "strawberry" <zac.ca...@gmail.com> wrote: > > > > On Apr 14, 10:05 pm, "Paul Lautman" <paul.laut...@btinternet.com> > > > wrote: > > > > > ros wrote: > > > > > I am completely clueless about the following query. > > > > > > - - Where does the longest flight go? > > > > > That depends on where it is leaving from. From where I am I think it is > > > > Australia. > > > > > > 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 CHARSET=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 CHARSET=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 > > > > > The tables are very interesting, but what do you want us to help with? > > > > :-) > > > > isn't this a clue? > > > > `depart` datetime default NULL, > > > `arrive` datetime default NULL- Hide quoted text - > > > > - Show quoted text - > > > I was thinking of "longest" as in distance! > > I know Sorry about the confusion but actually it was the duration!
|
Linear Mode
