selecting from one mysql table (friends) and ordering the results by fields in another table (users)?

Question

How would I go about selecting from one mysql table (friends) and ordering the results by fields in another table (users)?

The tables are setup as follows:

CREATE TABLE `users` (
  `id` int(12) NOT NULL auto_increment,
  `first_name` varchar(100) NOT NULL default '',
  `last_name` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM;

CREATE TABLE `friends` (
  `id` int(12) NOT NULL auto_increment,
  `user_id` int(2),
  `mutual` int(2) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM;

basically what I need to do is pull in the user_ids from the friends table, match them to the users table and get the users data, then order the output by the following:

concat(users.first_name, users.last_name) ASC, friends.mutual DESC

Answer 1

You would use a JOIN to achieve this.

  SELECT * 
    FROM friends 
    JOIN users ON friends.user_id = users.id 
    ORDER BY concat(users.first_name, users.last_name) ASC, friends.mutual DESC

Answer 2

Select users.first_name, users.last_name, friends.mutual from users, friends where users.id = friends.id AND friends.mutual != 0;

May work, or you may need to tune it. I do not have env to give you exact query :)

Happy Mysqling :)

--Cheers

Answer 3

Try this one! I think it should work!

SELECT
friends.user_id, users.*
FROM
friends, users
WHERE
  friends.user_id = users.id
ORDER BY
  concat(users.first_name, users.last_name) ASC, friends.mutual DESC

Answer 4

Hi. I believe you want something like:

SELECT * FROM users
INNER JOIN friends ON users.id=friends.user_id
ORDER BY CONCAT(users.first_name, users.last_name) ASC, friends.mutual DESC

I hope this helps!

Answer 5

use

select concat(users.first_name,users.last_name) as name,friends.mutual as friends from users 
right join friends on friends.users_id = users.id order by name asc, friends desc

This should do the job.

Answer 6

you need read for detail inside this.