Member Avatar for 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(flight.id) AS numflight
->FROM city LEFT JOIN flight ON origincityid=origincityid.id
->GROUP BY destinationcityid;

  • 2 Contributors
  • 1 Reply
  • 81 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by tesuji
Member Avatar for tesuji

Hi

Despite whether your below select statement meets your requirments, it has a severe fault:

SELECT city.name, COUNT(flight.id) AS numflight
->FROM city LEFT JOIN flight ON origincityid=origincityid.id
->GROUP BY destinationcityid;

That is, all selected columns not being parameters of aggregate functions (e.g. count) must be enumerated in GROUP BY clause no matter mysql manuals are alleging the opposite. Therefore your GROUP BY clause should be: GROUP BY city.name (everything else is comparing apples and oranges).

Ah, what about your former thread "Trying to join 2 tables in mysql-Help", really solved ?

-- tesu

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.