MySQL query help

[MasterKill]

I'm trying to create an advanced rank system on my clansite. But I can't figure out if this is possible with 1 query:

I would nee something like:

SELECT users.*, ranks.* FROM users LEFT JOIN ranks ON ? WHERE users.id = '1' LIMIT 1

? = where ranks.primair is the highest value. But this has to be done by looking at what ranks are linked to users.id 1

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL auto_increment,
  `username` varchar(50) NOT NULL default '',
  `realname` varchar(50) NOT NULL default '',
  `password` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM AUTO_INCREMENT=54 ;

CREATE TABLE IF NOT EXISTS `user_rank` (
  `id` int(11) NOT NULL auto_increment,
  `user_id` int(11) NOT NULL default '0',
  `rank_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM AUTO_INCREMENT=2 ;

CREATE TABLE IF NOT EXISTS `ranks` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(30) NOT NULL default '',
  `color` varchar(6) NOT NULL default '',
  `image` varchar(50) NOT NULL default '',
  `primair` int(11) NOT NULL default '999',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM AUTO_INCREMENT=5 ; 

So for example if I want to load all primair info for user $1;

and that should return me something like

$user = mysql_fetch_assoc(mysql_query($query));

print_r($user);

{
  id => 1
  name => masterkill
  title => administrator
  color => D00000
  // and some more obviously
} 

So I want to select a user by id, and load the rank info where the stats.primair is the highest value, but It must be linked to the profile!

Help would be appreciated.

[nielsie95]

SELECT users.*, ranks.* FROM users LEFT JOIN user_rank ON users.id = user_rank.user_id LEFT JOIN ranks ON user_rank.rank_id = ranks.id WHERE users.id = '1' ORDER BY ranks.primair DESC LIMIT 1

[MasterKill]

Omg great, worked like a charm!

I hope I can do those myself in the future, thanks for the quick help man!

This may be closed now

EDIT: oh I only had to remove the DESC since the lowest value should be the primair one, you couldn't know just thanks!