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;
mysql help - left join
Moderator: General Moderators
- superdezign
- DevNet Master
- Posts: 4135
- Joined: Sat Jan 20, 2007 11:06 pm
Re: mysql help - left join
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!
Re: mysql help - left join
Code: Select all
SELECT name, origincityid FROM city LEFT JOIN flight ON city.id = flight.origincityid