Query with multi tables

Question

I have four tables:

• characters
• arena_team
• arena_table_member
• arena_team_stats.

characters table has guid, name

arena_team table has arenateamid, name, type

arena_table_member table has guid(this is the same as in characters table), arenateamid

arena_team_stats table has arenateamid, rating, wins, wins2, played

How do I get the list of arena teams where character is? I tried:

$result=mysql_query("SELECT 
          characters.guid
           , characters.name
           , arena_team.arenateamid
           , arena_team.name
           , arena_team_stats.rating
           , arena_team_stats.wins
           , arena_team_stats.wins2
           , arena_team_stats.played
           , arena_team.type 
   FROM characters
           , arena_team_stats
           , arena_team 
    WHERE characters.name LIKE '%$q%' 
    AND arena_team.arenateamid = arena_team_stats.arenateamid 
    ORDER BY arena_team_stats.rating DESC") 
or die(mysql_error());

but it returns all arena teams which are in arena_team table.

Answer 1

Looks like you're missing a JOIN. It's easier to read with table aliases and formatted:

    SELECT c.guid
         , c.name
         , at.arenateamid
         , at.name
         , at.type
         , ats.rating
         , ats.wins
         , ats.wins2 
         , ats.played
      FROM characters c
INNER JOIN arena_team_member atm ON atm.guid = c.guid
INNER JOIN arena_team at ON at.arenateamid = atm.arenateamid
INNER JOIN arena_team_stats ats ON ats.arenateamid = at.arenateamid
     WHERE c.name LIKE '%$q%'
  ORDER BY ats.rating DESC