Tutorial :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!


Solution: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;  


Solution: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  


Solution: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;  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »