MySQL Left Join Help

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. Anyways, this is the query that I've come up with, but I'm pretty sure that it won't work. I would appreciate it if someone could help point out anything i need to add, etc.

SELECT cr.id           AS crid, 
       cr.title        AS rank_titl开发者_Python百科e, 
       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 

---

I think I might understand what your goal. If so, I don't think you mean you want to group by clan rank, as you state - I think you're asking to join based on clan rank.

SELECT rm.memberid     AS member_id, 
       rm.rosterxp     AS roster_xp, 
       rm.gameid       AS game_id,
       cr.id           AS crid, 
       cr.title        AS rank_title, 
       cr.minimumxp    AS rank_min, 
       cr.abbreviation AS rank_abbr, 
       cr.image        AS rank_image
FROM roster_members AS rm
LEFT JOIN clan_ranks AS cr ON cr.minimumXP = 
    (SELECT crm.minimumXP
        FROM clan_ranks crm
        WHERE crm.minimumXP < rm.rosterXP
        ORDER BY minimumXP DESC limit 1)
WHERE rm.gameID = :game_id
ORDER BY rm.rosterXP DESC 

The LEFT JOIN is only necessary if you have values in roster_members.rosterXP that are lower than your lowest clan_ranks.minimumXP. If that is not the case, than an INNER JOIN would suffice.

The difference between LEFT, RIGHT and INNER joins are as follows:

• An INNER JOIN will limit the results to records that fully match 'both' sides (records in the table on the 'left' and on the 'right' of the join statement exist based on the ON clause)
• A LEFT JOIN states that all the records for the table(s) specified to the 'left' of the join will be included, even if the records don't exist for the table(s) on the 'right'. If the records don't exist, NULL values will be substituted for each of the fields specified for that table.
• A RIGHT JOIN is just the reverse of a LEFT JOIN.

Note that I re-arranged the positions of the fields. This is for readability, based on the possibility of records in clan_ranks not existing. If they don't exist, first fields you see will contain data (from roster_members), and fields further to the right in the result set will be NULL. This is not a requirement by any means - just a convention.

Also, I removed the database prefixes and added a placeholder for readability.

---

The tricky thing here is the relationship between members and clan ranks. Suppose clan ranks contains

title       minxp
------      ------
chieftan    100
warrior     50
serf        5

The someone with 120 points would presumably be a chieftan. But trying to implement this as an SQL query which only returns a rank of chieftan is a bit tricky. Indeed, although it's possible to write an SQL query to return this, life is just too short.

The problem is massively simplified by changing the structure of the clan ranks table to title, minxp, maxxp. It's a bit of an overhead to maintain the data but makes the queries much, MUCH simpler:

title       minxp    maxxp
------      ------   -----
chieftan    100      999999999
warrior     50       100
serf        5        50

 SELECT ....
 FROM ".DB_PREFIX."roster_members AS rm
 LEFT JOIN ".DB_PREFIX."clan_ranks AS cr 
   ON (rm.rosterXP >= cr.minimumXP AND rm.rosterXP < cr.maximumXP)
 WHERE rm.gameID = ".$gameID."
 GROUP BY cr.id
 ORDER BY rm.rosterXP DESC 

Alternatively you could use a lookup function....

 CREATE FUNCTION getrank(p_xp INTEGER) 
     RETURNS varchar(50) CHARSET ascii
 READS SQL DATA
 BEGIN
   DECLARE l_rank VARCHAR(50);

   SELECT title
   INTO l_rank
   FROM clan_ranks
   WHERE minxp<p_xp
   ORDER BY minxp DESC
   LIMIT 0,1;

   RETURN l_rank;
 END;