Incredibly hard join (for me)

Question

I'm trying to make a web interface for a c++ game a friend have done. Unfortuantly Im not a php/sql guru.

Its especially hard when I need to join tables. Im not good with those.

To show you what im looking for: (i want to get all the members of a guild)

Guilds > The Badasses
-------------------------------
Rank      Player name
-------------------------------
Big Boss | Midvalley the Hornfreak

Loser    | Kraven the Hunter
           Saint Sinner
           Kull the Conqueror
           Zazi The Beast

Novice   | Igos du Ikana
--------------------------------

The tables look like this:

CREATE TABLE `guilds` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`founder` int(11) NOT NULL,
PRIMARY KEY (`id`)
)

CREATE TABLE `guild_ranks` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`guild_id` int(10) unsigned NOT NULL,
`name` varchar(255) NOT NULL,
`access` int(11) NOT NULL
)

CREATE TABLE IF NOT EXISTS `players` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`account_id` int(10) unsigned NOT NULL,
`rank_id` int(10) unsigned NOT NULL
)

So lets say YOU wanted to get all guild members of guild ID 114, how would you do it?

Thanks alot!

Answer 1

SELECT guild_ranks.name, players.name
FROM guild_ranks 
JOIN players
ON guild_ranks.id = players.rank_id -- at least that's how I assume they're joined...
WHERE guild_ranks.guild_id = 114
ORDER BY guild_ranks.name;

Answer 2

The complete join:

select `p`.* from `players` `p`
              LEFT JOIN `guild_ranks` `r` ON (`p`.`rank_id`=`r`.`id`)
              LEFT JOIN `guilds` `g` ON (`r`.`guild_id`=`g`.`id`)
              WHERE `guild_id`=114

life would be easier if you named id in the guild table guild_id, etc, so it would look like

select `players`.* from `players`
              LEFT JOIN `guild_ranks` USING (`rank_id`)
              LEFT JOIN `guilds` USING (`guild_id`)
              WHERE `guilds`.`guild_id`=114

Answer 3

This ought to do it.

   SELECT p.* 
    FROM players p 
      JOIN guild_ranks gr ON (p.rank_id=gr.id)
      JOIN guilds g ON (gr.guid_id=g.id)
    WHERE g.id=114;