mysql help - left join

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
canadian_angel
Forum Commoner
Posts: 31
Joined: Fri Jun 11, 2010 3:13 pm

mysql help - left join

Post by canadian_angel »

I need to expand on the first query by counting the number of flights from the citys in
my airline database and so far have come up with the second query;

The tables in the database are flight, and city.
flight columns:number, origincityid,destinationcityid,departure,duration and stops
city columns:id and name

SELECT city.name, flight.origincityid FROM city
LEFT JOIN flight ON city.id=origincityid


SELECT city.name, COUNT(origincityid.id) AS numflight
->FROM city LEFT JOIN flight ON origincityid=origincityid.id
->city.name;
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Re: mysql help - left join

Post by superdezign »

You can count related rows by using COUNT() and GROUP BY in MySQL, and giving the COUNT() value an alias by using "AS". I'd expand, but my gf is rushing me. Google it!
User avatar
Alex-V
Forum Newbie
Posts: 17
Joined: Mon Jul 19, 2010 3:53 pm

Re: mysql help - left join

Post by Alex-V »

Code: Select all

SELECT name, origincityid FROM city LEFT JOIN flight ON city.id = flight.origincityid
If im not wrong this is how that query should look like
Post Reply