Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    New Coder
    Join Date
    Dec 2011
    Posts
    11
    Thanks
    9
    Thanked 1 Time in 1 Post

    Need Help With A Table Join

    I am trying to join two tables so that I can grab the information and order them by the members points. Here is the query I am trying to use:

    Code:
          
    $members_info = mysqli_query($connection, "
    SELECT 
    team_member,
    stage,
    points
    FROM team_member_scores
    RIGHT JOIN team_members
    ON team_members.team_id = team_member_scores.team_id
    WHERE game_id = '".$game_id_info."'
    AND team_member_scores.team_id = '".$team_id."'
    GROUP BY team_member
    ORDER BY points DESC
    LIMIT 5") 
    or die(mysqli_error($connection));
    This query pulls out all the team members correctly, but instead of grouping them and then ordering them by their points, it seem to just sum the points for all the members and lists that same summed number for every member. I need to pull out the info from both tables because I need to order them in a specific way, and not all team members will show up in the team_member_scores table if they didn't participate in the last game.

    I would like to pull every match from the team members table that has the team id, regardless if they are in the team_member_scores table. I can get it to pull out the right information only from the team_members table. I can run two queries, but then I wont' get the sort order I am hoping for.

    Anyway, if you need more info, please let me know. Any help and suggestions are greatly appreciated.
    Last edited by Bob Fo; 03-07-2013 at 07:17 PM.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,458
    Thanks
    76
    Thanked 4,373 Times in 4,338 Posts
    What POSSIBLE reason do you have for using GROUP BY in that query?

    That's the culprit.

    KILL IT.

    Any other DB wouldn't allow you to use it like that, but MySQL not only allows it but makes it have some very weird side effects.

    ***********

    Incidentally, your use of RIGHT JOIN there is incorrect. Or, rather, because of the rest of the query your RIGHT JOIN is silently being converted into an INNER JOIN.

    You can *NOT* use any condition on the DEPENDENT table in an outer join in the WHERE clause. Such conditions *MUST* be made in the ON clause. (Sole exceptions: tests for NULL or NOT NULL.)

    So:
    Code:
    SELECT team_member, stage, points
    FROM team_member_scores
    RIGHT JOIN team_members
        ON team_members.team_id = team_member_scores.team_id
           AND team_member_scores.team_id = '".$team_id."'
    WHERE game_id = '".$game_id_info."'
    ORDER BY points DESC LIMIT 5
    Though I would bet a latte that you do not need or want the apostrophes around '$team_id' or '$game_id_info'. You should never put apostrophes around NUMBERS if you are comparing with or assigning to numeric fields.

    May I *strongly* suggest that before you use a query in your PHP code you first check it out using some DB query tool? MySQLWorkbench, perhaps. Or even ugly MySQLPHPAdmin or whatever.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    Bob Fo (03-07-2013)

  • #3
    New Coder
    Join Date
    Dec 2011
    Posts
    11
    Thanks
    9
    Thanked 1 Time in 1 Post
    Thanks again Old Pedant, I learn something new each time I post on this forum, and for that I am very grateful.

    The only issue is that it shows the same amount of points for every team member pulled from the table. In other words, it doesn't seem to assign the right points to the right member when pulling them from the table, if that makes sense.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,458
    Thanks
    76
    Thanked 4,373 Times in 4,338 Posts
    Can you show some sample data?

    Maybe just the data for one team?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    Bob Fo (03-07-2013)

  • #5
    New Coder
    Join Date
    Dec 2011
    Posts
    11
    Thanks
    9
    Thanked 1 Time in 1 Post
    Quote Originally Posted by Old Pedant View Post
    Can you show some sample data?

    Maybe just the data for one team?
    Sure, and sorry for not providing this in the first post:

    The team_member_scores has the following columns and data:

    username - game_id - team_id - points
    Fred - 1 - 1 - 200
    Joe - 1 - 1 - 600
    Aaron - 1 - 2 - 500


    and the team_members has the following info:

    team_id - username - stage - join_date
    1 - Fred - 1 - timstamp

    The username is being used as the foreign key in the team_member_scores table (I didn't design the team_members table, but I can redesign it if you think it would be better.)

    I hope that clears it up.

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,458
    Thanks
    76
    Thanked 4,373 Times in 4,338 Posts
    The username is being used as the foreign key in the team_member_scores table
    Ummm...no, it is not. At least not in your query.

    And that's surely the problem.

    Foreign keys are *NOT* automatically used in queries. Ever. They *MUST* be explicitly specified in the JOIN or they will be ignored. A foreign key DECLARATION in the table is only useful for preserving data integrity, not for queries.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    Bob Fo (03-08-2013)

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,458
    Thanks
    76
    Thanked 4,373 Times in 4,338 Posts
    The real problem here is that the team_id field in the team_member_scores table is extraneous. It was leading you down the garden path!

    WHERE, though, did the field member_name come from? In what you just showed me, there is no such field? Now you seem to be using username instead? I'll go with that, but...

    May I try completely rewriting the query, then?
    Code:
    $sql = "
    SELECT M.username, M.stage, S.points
    FROM team_members AS M
    LEFT JOIN team_member_scores AS S ON M.username = S.username
    WHERE M.team_id = $team_id AND M.game_id = $game_id_info
    ORDER BY points DESC LIMIT 5";
    (You don't need to use string concatenation with the period; PHP allows you to drop PHP variables into the middle of a quoted string so long as your use "..." instead of '...' for the quotes.)

    Now, that will *NOT* get you the SUM of points for each team_member. That will just show you who scored the most points a SINGLE GAME.

    Since you have limited the query to just one game_id that's probably okay.

    Variations on that:
    Code:
    $sql = "
    SELECT M.username, M.stage, SUM(S.points) AS totalPoints
    FROM team_members AS M
    LEFT JOIN team_member_scores AS S ON M.username = S.username
    WHERE M.team_id = $team_id 
    GROUP BY M.username, M.stage
    ORDER BY points DESC LIMIT 5";
    And that will get you the total points scored by all members of a given team in all games. (Note that I omitted that AND M.game_id = ... here. Note also that both fields are needed in the GROUP BY, as shown.)
    Last edited by Old Pedant; 03-08-2013 at 06:39 AM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    Bob Fo (03-08-2013)

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,458
    Thanks
    76
    Thanked 4,373 Times in 4,338 Posts
    And almost forgot to say: That will show all team members, even if they have no scores at all in the team_member_scores table. Your query would have, too, except for the one bogus condition in the WHERE clause and if the JOIN had been done correctly.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    Bob Fo (03-08-2013)

  • #9
    New Coder
    Join Date
    Dec 2011
    Posts
    11
    Thanks
    9
    Thanked 1 Time in 1 Post

    Thumbs up

    Quote Originally Posted by Old Pedant View Post
    The real problem here is that the team_id field in the team_member_scores table is extraneous. It was leading you down the garden path!

    WHERE, though, did the field member_name come from? In what you just showed me, there is no such field? Now you seem to be using username instead? I'll go with that, but...

    May I try completely rewriting the query, then?
    Code:
    $sql = "
    SELECT M.username, M.stage, S.points
    FROM team_members AS M
    LEFT JOIN team_member_scores AS S ON M.username = S.username
    WHERE M.team_id = $team_id AND M.game_id = $game_id_info
    ORDER BY points DESC LIMIT 5";
    (You don't need to use string concatenation with the period; PHP allows you to drop PHP variables into the middle of a quoted string so long as your use "..." instead of '...' for the quotes.)

    Now, that will *NOT* get you the SUM of points for each team_member. That will just show you who scored the most points a SINGLE GAME.

    Since you have limited the query to just one game_id that's probably okay.

    Variations on that:
    Code:
    $sql = "
    SELECT M.username, M.stage, SUM(S.points) AS totalPoints
    FROM team_members AS M
    LEFT JOIN team_member_scores AS S ON M.username = S.username
    WHERE M.team_id = $team_id 
    GROUP BY M.username, M.stage
    ORDER BY points DESC LIMIT 5";
    And that will get you the total points scored by all members of a given team in all games. (Note that I omitted that [icode]AND M.game_id = ...[/code] here. Note also that both fields are needed in the GROUP BY, as shown.)
    Thanks again! Yeah, I changed it from member_name to username just to show that they were referring to the same info, but forgot to mention that much needed fact when I posted. Sorry about that.

    Again, the info you posted is gold to me!


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •