help with query

Hi,

I need help in writing a query.

The following query uses 2 tables and my try is pasted below; I don't
understand how to use count, max functions here.

"return the name of the airport with the most flights departing from
it. Only return one row and one column of data."

select a.name
from flight_profiles f, airports a
where f.flt_from = a.iata_code
and f.flt_from
order by f.flt_from DESC
LIMIT 1;

CREATE TABLE `my-database`.`airports` (
`IATA_CODE` varchar(3) default NULL,
`NAME` varchar(30) default NULL,
`CTY_IATA_CODE` varchar(3) default NULL,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

Thanks in advance.
ros

== Quote from ros (ros060@gmail.com)'s article
> Hi,
> I need help in writing a query.
> The following query uses 2 tables and my try is pasted below; I don't
> understand how to use count, max functions here.
> "return the name of the airport with the most flights departing from
> it. Only return one row and one column of data."
> select a.name
> from flight_profiles f, airports a
> where f.flt_from = a.iata_code
> and f.flt_from
> order by f.flt_from DESC
> LIMIT 1;
> CREATE TABLE `my-database`.`airports` (
> `IATA_CODE` varchar(3) default NULL,
> `NAME` varchar(30) default NULL,
> `CTY_IATA_CODE` varchar(3) default NULL,
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> CREATE TABLE `my-database`.`flight_profiles` (
> `FLP_ID` decimal(8,0) default NULL,
> `FLT_NUMB` decimal(4,0) default NULL,
> `SEATS` int(11) default NULL,
> `PRICE_CODE` varchar(3) default NULL,
> `FLT_FROM` varchar(3) default NULL,
> `FLT_TO` varchar(3) default NULL,
> `depart` datetime default NULL,
> `arrive` datetime default NULL
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> Thanks in advance.
> ros

this is relatively simple:
select name from airports join flight_profiles on flt_from = iata_code group by
iata_code order by count(iata_code) desc limit 1;

hope this helps

On Apr 25, 2:41 pm, lark <ham...@sbcglobal.net> wrote:
> == Quote from ros (ros...@gmail.com)'s article
>
>
>
> > Hi,
> > I need help in writing a query.
> > The following query uses 2 tables and my try is pasted below; I don't
> > understand how to use count, max functions here.
> > "return the name of the airport with the most flights departing from
> > it. Only return one row and one column of data."
> > select a.name
> > from flight_profiles f, airports a
> > where f.flt_from = a.iata_code
> > and f.flt_from
> > order by f.flt_from DESC
> > LIMIT 1;
> > CREATE TABLE `my-database`.`airports` (
> > `IATA_CODE` varchar(3) default NULL,
> > `NAME` varchar(30) default NULL,
> > `CTY_IATA_CODE` varchar(3) default NULL,
> > ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> > CREATE TABLE `my-database`.`flight_profiles` (
> > `FLP_ID` decimal(8,0) default NULL,
> > `FLT_NUMB` decimal(4,0) default NULL,
> > `SEATS` int(11) default NULL,
> > `PRICE_CODE` varchar(3) default NULL,
> > `FLT_FROM` varchar(3) default NULL,
> > `FLT_TO` varchar(3) default NULL,
> > `depart` datetime default NULL,
> > `arrive` datetime default NULL
> > ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> > Thanks in advance.
> > ros
>
> this is relatively simple:
> select name from airports join flight_profiles on flt_from = iata_code group by
> iata_code order by count(iata_code) desc limit 1;
>
> hope this helps

Thanks Lark for the help. Really appreciate that.