Join Help

[ShoeLace1291]

Ok, so I'm using PHP and MySQL to create a clan roster page.  I have several different tables that I need to use for this query.  Here's how my tables are laid out:

 

Members - regular members table

roster_members - memberID, gameID, rosterXP(experience points the member has earned for that roster)

clan_ranks - title, minimumXP

roster_games - table for particular games which members are a part of

 

So what I want to do is to create a query that groups members by clan rank(minimumXP in descending order).  Their clan rank is determined by what clan_rank minimumXP is lower than the roster_member(WHERE minimumXP < rosterXP LIMIT 1).  How would I create something like this?  I assume I would need to use either a left join or a right join... not sure what the difference is.  Any help would be appreciated.

 

[ShoeLace1291]

So this is what I have come up with but I am pretty sure that it will not work. 

 

SELECT cr.id AS crID, cr.title AS rank_title, cr.minimumXP AS rank_min, cr.abbreviation AS rank_abbr, cr.image AS rank_image,
			rm.memberID AS member_id, rm.rosterXP AS roster_xp, rm.gameID AS game_id
		FROM ".DB_PREFIX."roster_members AS rm
		LEFT JOIN ".DB_PREFIX."clan_ranks AS cr ON (cr.minimumXP < rm.rosterXP)
		WHERE rm.gameID = ".$gameID."
		GROUP BY cr.id
		ORDER BY rm.rosterXP DESC

[fenway]

What doesn't work is getting back non-group-by fields.