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 6 of 6
  1. #1
    New Coder
    Join Date
    Sep 2013
    Posts
    20
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Joining one table with another based on MAX value in column.

    Hi,
    I have a table called "game" and a table called "move" similar to this: game{ game_id, game_name } and move{ move_id, game, the_move }. Where game_id and move_id are auto increment values and the 'game' field in the move table is a foreign-key for the game-table.

    Now i want to select all games and join to that the last move that was made in that game (thus has the highest move_id for that game). Example game table:
    Code:
    1, "game1name"
    2, "game2name"
    3, "game3name"
    Example movetable:
    Code:
    1, 1, walkleft
    2, 1, jump
    3, 2, jump
    4, 2, walkright
    Then i want to join the tables in such a way that the result is like this:
    Code:
    1, "game1name","jump"
    2, "game2name","walkright"
    Because jump is the last move for game1 and "walkright" is the last move for game2.

    So far i got this:
    Code:
    SELECT 
    	game.game_id,
    	game.game_name,
    	move.the_move
    FROM game as game 
    LEFT JOIN move as move 
    ON game.game_id=move.game
    But this selects all games with all move combinations I think and I dont know how to narrow the selection such that all games are returned once with the last move data included. Note this is just an example, im applying this to something totally different but the problem is the same.

    Hope you can help me out.
    Thanks!

  • #2
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,125
    Thanks
    2
    Thanked 327 Times in 319 Posts
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #3
    New Coder
    Join Date
    Sep 2013
    Posts
    20
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Thanks for the reply.

    I have the feeling the solution for me is not on that link.

    There are 3 solutions there. The first one doesn't join tables so I think that can't help me.

    The second one uses a inner join, but I need a left join.

    The third one (and also the second one) relies on a common field in both tables: s1.price < s2.price or s1.price = s2.price. In my case I dont have a reference to the moveId in the game table.

    Maybe I am overlooking something or mistaking, please let me know

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,118
    Thanks
    80
    Thanked 4,555 Times in 4,519 Posts
    It would HELP if you would give us the FULL SCHEMA of both tables!!!

    I am assuming this is the schema for the MOVE table:
    Code:
    move_id, game_id, move
    1, 1, walkleft
    2, 1, jump
    3, 2, jump
    4, 2, walkright
    and then it's trivial:
    Code:
    SELECT 
    	game.game_id,
    	game.game_name,
    	IFNULL(move.the_move,'N/A') AS theMove
    FROM game 
    LEFT JOIN (
            SELECT MAX(movei_d) AS maxmoveid, game_id
            FROM move 
            GROUP BY move_id ) AS mm
    ON game.game_id = mm.game_id
    LEFT JOIN move ON mm.maxmoveid = move.move_id
    Think thatis right. Untested, off the top of my head.
    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:

    stefan1 (12-11-2013)

  • #5
    New Coder
    Join Date
    Sep 2013
    Posts
    20
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Oh sorry I should have posted the fieldnames in the example tables that I gave.
    The schemas were:
    move{ move_id, game, themove }
    game{ game_id, game_name }

    Anyway thanks, that's what I needed! Though you grouped by move_id instead of game_id. So I changed it to:
    Code:
    SELECT 
    	game.game_id,
            game.game_name, 
    	IFNULL( move_id,  'N/A' ) AS themove
    FROM game
    LEFT JOIN (
    	SELECT MAX( move.move_id ) AS maxmoveid, move.game
    	FROM move
    	GROUP BY move.game
    ) AS mm 
    ON game.game_id = mm.game
    LEFT JOIN move ON mm.maxmoveid = move.move_id
    Which works, so thanks alot!

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,118
    Thanks
    80
    Thanked 4,555 Times in 4,519 Posts
    Yep, DOH on me! Can't GROUP BY the same field you are getting an aggregate value of. Double DOH.
    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.


  •  

    Posting Permissions

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