Problem with query

Question

I've four tables:

• characters
  • guid
  • name
  • gender
  • class
  • race
  • online
• character_arena_stats
  • guid
  • personal_rating
  • matchmaker_rating
• arena_team_member
  • arenateamid
  • played_season
  • played_week
  • wons_season
  • wons_week
• arena_team
  • arenateamid
  • captain_guid

and I need to get character details(race,class,name,gender,online) and team information(personal_rating,matchmaker_rating,played_season,played_week,wons_season,wons_week,captain_guid), but can't get it working. My query is:

$result=mysql_query("SELECT 
            c.guid,
            c.name,
            c.gender,
            c.class,
            c.online,
            c.race,
            atm.guid,
            atm.played_season,
            atm.played_week,
            atm.wons_season,
            atm.wons_week,
            atm.arenateamid,
            cas.personal_rating,
            cas.guid,
            cas.matchmaker_rating,
            at.arenateamid,
            at.captainguid
         FROM
         character_arena_stats cas,
         arena_team_member atm,
         characters c,
         arena_team at
         WHERE c.guid = cas.guid AND atm.arenateamid = ".$entry." AND at.arenateamid = ".$entry."");

It should return only members whose guid is equal to c.guid, cas.guid, atm,guid and those, whose atm.arenateamid is equal to at.arenateamid. Insted, it returns a lot of random members.

Thanks and sorry for my english.

Answer 1

Since you're not specifying how records in the arena tables should join to records in the character tables, you're getting a cross join, which returns every combination of character records with arena records.

When you say "I want to get them all," what exactly do you mean? Find a starting point for your query. For example: are you looking for all characters, organized by team, with their details and arena stats? Or, for each character, all the teams on which they participate?

Defining the requirements a little more clearly will help us suggest solutions. :)

Update: Actually, having read the query a little more closely, I believe I can infer what you're looking for:

SELECT 
            c.guid,
            c.name,
            c.gender,
            c.class,
            c.online,
            c.race,
            atm.guid
            atm.played_season,
            atm.played_week,
            atm.wons_season,
            atm.wons_week,
            atm.arenateamid,
            cas.personal_rating,
            cas.guid,
            cas.matchmaker_rating,
            at.arenateamid,
            at.captainguid
         FROM
         character_arena_stats cas,
         arena_team_member atm,
         characters c,
         arena_team at
         WHERE c.guid = cas.guid 
         and c.guid = atm.guid
         and atm.arenateamid = at.arenateamid
         AND at.arenateamid = ".$entry."

Note that the Arena Team and Character tables are now joined based on the team captain's GUID - this will avoid the cross join ("random rows") problem. Also, Arena Team Members is now joined to Arena Teams, and the filter parameter is only checked against the Teams table.

Not sure this will give you precisely what you want without knowing more about your data and requirements - I believe what it will give you is a list of each team captain, their arena stats, along with their team and team members' stats. Hopefully this will move you forward. Good luck!

Answer 2

uhh mate not sure what you got there,... to lazy myself to write the query for you, have a look again at dev.mysql.com refs should be straight forwared.

also your *character_arena_stats* table, shouldn't there be a ref to a arena table or something?

• guid
• arena_id ?
• personal_rating
• matchmaker_rating

see more here for normalization

Answer 3

Yeah, I am not really sure exactly what you're trying to do, but based on the description ...

Your Model seems to be all wrong and will never produce the results you are looking for. For instance, there are no Keys tying arena_team and arena_team_member to characters and character_arena_stats.

Secondly, this condition:

"WHERE c.guid = cas.guid AND atm.arenateamid = ".$entry." AND at.arenateamid = ".$entry);

is incorrect for this statement: "It should return only members ... whose atm.arenateamid is equal to at.arenateamid".

Rather, it could be rewritten as follows:

"WHERE c.guid = cas.guid AND atm.arenateamid = at.arenateamid AND atm.arenateamid = ".$entry);

Regardless though, because of the aforementioned reasons, the query will never returned expected results, at least based on what I understood from your post.

SIDE NOTE: This is PHP code, so I do not know why you are tagging it as jQuery.

Good Luck,