On Apr 14, 11:56 am, "ros" <com> wrote:
Try:
SELECT c.name,count(a.*) AS no_of_airports
FROM airports a
LEFT JOIN cities c ON c.IATA_CODE = a.CTY_IATA_CODE
GROUP BY a.CTY_IATA_CODE
Hi, I have 2 tables ( cities and airports) in a database and I want to run a query that gives me the city with the largest no. of airports. But I am a little confused how to do it. I used this, although it doesn't make much sense to me: select count(c.name) from airports a, cities c where c.iata_code = a.cty_iata_code; The sql scripts of the 2 tables are as follows: 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; CREATE TABLE `my-database`.`airports` ( `IATA_CODE` varchar(3) default NULL, ...
Hi,
I have 2 tables ( cities and airports) in a database and I want to run
a query that gives me the city with the largest no. of airports. But I
am a little confused how to do it.
I used this, although it doesn't make much sense to me:
select count(c.name)
from airports a, cities c
where c.iata_code = a.cty_iata_code;
The sql scripts of the 2 tables are as follows:
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;
CREATE TABLE `my-database`.`airports` (
`IATA_CODE` varchar(3) default NULL,
`NAME` varchar(30) default NULL,
`CTY_IATA_CODE` varchar(3) default NULL,
`UTC_OFFSET` decimal(2,0) default NULL,
`ICAO_CODE` varchar(4) default NULL,
`ROUTE_SWARE_APT_NME` varchar(100) default NULL
) ENGINE=InnoDB DEFAULT CHT=latin1;
I would be really thankful for help with this query.
Thanks
ros
On Apr 14, 11:56 am, "ros" <com> wrote:
Try:
SELECT c.name,count(a.*) AS no_of_airports
FROM airports a
LEFT JOIN cities c ON c.IATA_CODE = a.CTY_IATA_CODE
GROUP BY a.CTY_IATA_CODE
On Apr 14, 4:04 am, "strawberry" <com> wrote:
>
>
>
>
>
>
>
>
> Try:
>
> SELECT c.name,count(a.*) AS no_of_airports
> FROM airports a
> LEFT JOIN cities c ON c.IATA_CODE = a.CTY_IATA_CODE
> GROUP BY a.CTY_IATA_CODE[/ref]
Thanks for the reply Strawberry but I get the following error when I
run this command:
You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near '*) AS no_of_airports
FROM airports a
LEFT JOIN cities c ON c.IATA_CODE = a.CTY' at line 1
On Apr 14, 12:10 pm, "ros" <com> wrote:
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
>
>
>
> Thanks for the reply Strawberry but I get the following error when I
> run this command:
>
> You have an error in your SQL syntax; check the manual that
> corresponds to your MySQL server version for the right syntax to use
> near '*) AS no_of_airports
> FROM airports a
> LEFT JOIN cities c ON c.IATA_CODE = a.CTY' at line 1[/ref]
just remove a.
On Apr 14, 4:50 am, "strawberry" <com> wrote:
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
>
>
>
> just remove a.[/ref]
Thanks again for the reply Strawberry.
I tried what you suggested but when I run the query without the a and
just (*) it gives me all cities with the no. of airports in each one.
While I want the city with the highest no. of airports.
Can you see what the problem is?
Thanks
ros
On 15 Apr 2007 01:27:55 -0700, "ros" <com> wrote:
Add :
ORDER BY no_of_airports DESC
LIMIT 1;
at the end.
On Apr 15, 1:54 am, subtenante <com> wrote:
>
> Add :
>
> ORDER BY no_of_airports DESC
> LIMIT 1;
>
> at the end.[/ref]
Thanks a lot Sub. That solved the puzzle!
Cheers
ros
Bookmarks