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 4 of 4
  1. #1
    Regular Coder
    Join Date
    Apr 2007
    Posts
    317
    Thanks
    24
    Thanked 3 Times in 3 Posts

    Need help combining queries

    Hi Guys,

    I'm not good with advanced mysql queries. What i am looking to do is combine the following two queries, if possible.

    Query 1
    Code:
    SELECT * FROM game_drawings WHERE game_id = [game_id] GROUP BY drawing_date ORDER BY drawing_date DESC [custom limit]
    Query 2
    Code:
    SELECT * FROM game_results WHERE drawing_id = [drawing_id] and game_id = [game_id]
    The common key between tables would be drawing_id.
    Thanks in advance.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,023
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Code:
    SELECT * FROM game_drawings AS D, game_results AS R
    WHERE D.drawing_id = R.drawing_id 
    AND D.game_id = [game_id] 
    GROUP BY D.drawing_date 
    ORDER BY D.drawing_date 
    DESC [custom limit]
    BUT...

    What's the point of the GROUP BY in there??? GROUP BY is normally only useful when you have some sort of COMPOSITE function in your SELECT list. And then you need to GROUP BY every field that is *not* a composite.

    I strongly suspect that you do *NOT* want to use GROUP BY.

    Also, you should not do SELECT *, most especially when you have more than one table.

    Because now you will have *TWO* fields named [b]drawing_id{/b] (and possibly more pairs than that) so when you go to retrieve a field by name you will have ambiguity.
    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.

  • #3
    Regular Coder
    Join Date
    Apr 2007
    Posts
    317
    Thanks
    24
    Thanked 3 Times in 3 Posts
    LOL. Well i did mention that i sucked with advanced queries. You are correct i suppose i didn't need the group by. What i am looking to do is retrieve the drawing_dates from the game_drawings table, and all the game_results that matches that ID. So for example in game_results table there may be 20 entries with the same drawing_date. I need to retrieve them all.

    What i was doing prior to this was running the first query to get my drawing dates. Then in php i was doing a foreach loop, inside each iteration of the drawing date i was running the second query to get all the results for that date. What you gave me right now almost works. There are ambiguous keys like you mentioned but more importantly, the limit should only be on the drawing_date query. Its output right now limits the amount of rows returned.

    Thanks for the help. Very much appreciated.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,023
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Okay, that's a little tougher.
    Code:
    SELECT specific, list, of, fields
    FROM game_results AS R,
         ( SELECT drawing_id, game_id, other, fields
           FROM game_drawing 
           WHERE game_id = [game_id]
           ORDER BY drawing_date DESC
           LIMIT [custom limit] ) AS D
    WHERE D.drawing_id = R.drawing_id 
    ORDER BY D.drawing_date DESC, R.somefield
    That's the only way to get the LIMIT placed on the drawing_date alone.
    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
    •