View Full Version : Need help combining queries

10-23-2011, 11:00 PM
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

SELECT * FROM game_drawings WHERE game_id = [game_id] GROUP BY drawing_date ORDER BY drawing_date DESC [custom limit]

Query 2

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.

Old Pedant
10-23-2011, 11:07 PM
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]


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.

10-23-2011, 11:21 PM
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.

Old Pedant
10-24-2011, 02:22 AM
Okay, that's a little tougher.

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.