how to sort data with conditions?

[jasongr]

Hello

I have the following two tables

create table `people` (
  `ID` int unsigned not null,
  `First_Name` varchar(255) not null,
  `Last_Name` varchar(255) not null,
  `Nickname` varchar(255) not null,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

create table `acquaintance` (
  `Acquaintance_ID` int unsigned not null,
  `Person_ID` int unsigned not null,
  `Is_Friend` tinyint not null,
  PRIMARY KEY (`Acquaintance_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

The people table stored information on people in the application.
The acquaintance table stores information on people I met.
The join between the two tables is done via the Person_ID attribute in the acquaintance table and the ID attribute in the people table.

I would like to perform the following query:
Get the info on the people I know, sorted by name (see below the definition of what constitutes a name).
Here is the trick:
If a certain person is a friend of mine, I would SQL to compare his first name and then last name to the other peope. If the person isn't a friend of mine, the comparison should be based on his nickname:

Here is something I tried:

SELECT 
 people.*
FROM
 people, acquaintance
WHERE ID = Person_ID
ORDER BY case when (Is_Friend = 1) then First_Name, Last_Name else Nickname;

This isn't working
Can someone help me with the correct syntax?

thanks in advance

[r937]

ORDER BY case when Is_Friend = 1
then concat(First_Name, Last_Name) else Nickname end

[jasongr]

I think I managed to solve it differently.
Please tell me what you think of my solution:

order by case when (Is_Friend = 1) then First_Name else Nickname end, case when (Is_Friend = 1) then Last_Name end

[r937]

that works too, eh