Hi guys

I have a problem in sorting rows for a table that is connected to a
many-to-many table. Here is the structure:

Table Person

id, name
insert into Person values(1, 'mark');
insert into Person values(2, 'paul');

Table City

id, name
insert into City values(1, 'new york');
insert into City values(2, 'london');
insert into City values(3, 'madrid');

Table City_Person

id, city_id, person_id
insert into City_Person values(1, 1, 1);
insert into City_Person values(2, 3, 1);
insert into City_Person values(3, 2, 2);
insert into City_Person values(4, 3, 2);

So, if i want to retrieve all the people and order them by city_name,
how can I do this?
The result should be:

pauk
mark

As paul belongs to cities: london/madrid
while mark belonbgs to cities: new york/madrid

Alphabetically, london is the first one, before madrid and new york.
You see the point?

On 09-11-10 10:40, pac Man wrote:
I dont understand your problem,
Why do you want to list Persons, without the City...

maybe next 2 queries help...

mysql> select a.name, c.name from Person a inner join City_Person b on
a.id=b.person_id inner join City c on c.id=b.city_id order by c.name;
+------+----------+
4 rows in set (0.00 sec)

mysql> select a.name, c.name from Person a inner join City_Person b on
a.id=b.person_id inner join City c on c.id=b.city_id order by c.name,
a.name;
+------+----------+
4 rows in set (0.00 sec)


--
Luuk

On 9 Nov, 10:58, Luuk <L... (AT) invalid (DOT) lan> wrote:
To be honest, I need to retrieve eveything, like you said. But Then
Paul has to be shown only once. You get it?
Something like:

Paul - london/madrid
Mark madrid/new york

Right now Im using a bad solution, by getting all the cities for a
person by doind a loop inside the loop.

Maybe a solution can be getting everything and only show the person
once? Dont know if it is the best solution though.

Am 09.11.2010 10:40, schrieb pac Man:

Why do you have an extra id in this table? Normally the combination from
city_id and person_id would be the primary key. Or better maybe
person_id, city_id.

In JOINing the tables that have the necessary information:

What should be considered in sorting the result? Only the "first" city,
or the combination of all cities?

Suppose:

Paul -> London/Madrid/New York
Mark -> London/New York

Considering only the first city, the sorting would be undefined, because
in both cases, it's London.

Considering the combination of all cities, it would be Paul.

For the first case, here's a solution:

SELECT p.name
FROM Person p
[left] JOIN (
^^^^^^
City_Person cp JOIN City c ON cp.city_id = c.id

) ON p.id = cp.person_id

GROUP BY p.id
^^^^^^^^
ORDER BY MIN(c.Name)
^^^^^^^^^^^
The second case might be a little more complicated...

Helmut

On 09-11-10 11:29, pac Man wrote:
select a.id, a.name, t.cities
from Person a
left join (select a.id, GROUP_CONCAT(c.name) cities
from Person a
inner join City_Person b on a.id=b.person_id
inner join City c on c.id=b.city_id
group by a.id) t on a.id=t.id
order by cities;

But GROUP_CONCAT() is a MySQL specific function,
when you use this, make sure you dont have to convert your app. to
another database....

--
Luuk

On Tue, 9 Nov 2010 02:29:04 -0800 (PST), pac Man wrote:
Ah, see... in order to sort this way, you don't really care about any
city *other than* the first one alphabetically in the list. So a clever
mix of MIN(c.name) and GROUP BY may get you the results you're looking
for.

--
When C++ is your hammer, everything looks like a thumb.
-- Steven M. Haflich

Am 09.11.2010 15:55, schrieb Peter H. Coffin:
What do you mean by "other than the first one"? What about:

Paul - london/madrid
Mark - london/new york

Here the first one is equal...

Maybe I didn't get your point?

Helmut

On 12-11-10 10:40, Helmut Chang wrote:
The OP did not specify how to sort Persons that have the same City as
the first one in their list.

If it 'does not matter', its easy
If it 'does matter', the ORDER BY should be changed...

--
Luuk

Am 12.11.2010 20:22, schrieb Luuk:

....
Yes, that's true. But "I need to retrieve everything" gives me the
assumption, that all cities might be relevant in sorting.

And I was not sure, whether Peter had the same assumption in his answer
and his solution takes this into account, but I didn't understand it .

Helmut