MySQL query question

Help with the syntax of a complex MySQL query

[lruellan]

Hello,

I'm trying to setup a complicated query but I'm stuck and I am not sure if it is even possible to do it.
Here's the situation.

I have two tables: cities, routes.
'Cities' is basically a list of cities: paris, london, etc...

'Routes' has 3 columns: origin, destination, travelers.
Content for instance looks like:

Paris | London | 1512
Paris | Brussels | 789
Paris | Nice | 4856
etc...

I would like to setup a query that will return me for each city in my cities table, the most popular destination, based on the number of traveler.

I tried something like:

SELECT cities.origin, b.destination, b.travelers FROM cities, (SELECT * FROM routes WHERE routes.origin = cities.origin ORDER BY travelers DESC LIMIT) as b

But it doesnt work. Anybody would know how to achieve this?
Thank you in advance for any tip.

[Matthew1980]

Hi there IrueIIan,

As this could be a complex query, have you tried posting in the Sql forum at all? Just thought i should offer that suggestion.

Cheers,
MRb

[LifeinAsia]

I think something like this will work:
SELECT a.Origin, a.Destination, a.travelers
FROM Routes a INNER JOIN (SELECT origin, MAX(travelers) FROM Routes GROUP BY origin) AS b ON (a.origin=b.origin AND a.travelers=b.travelers)

One issue that I can see is that if 2 routes (same origin, different destination- for example, if both Paris-London and Paris-Brussels had 1512 travelers) have the same number of travelers, you'll get both routes. Technically, that's correct. But if you only want to show one route for each origin, then you'll have to add some logic in the PHP side of things to pick which one is the "top" when there are more than one tied for the top.

[LifeinAsia]

Oh, and if you need additional fields from the cities table, you'll need an additional JOIN to bring them in.

[lruellan]

Thank you so much for your feedback. I'm gonna try this.
My bad - I overlooked the SQL forum.