Merging sql queries to get different results by date

Question

I am trying to build a 'recent events' feed and can't seem to get either my query correct, or figure out how to possible merge the results from two queries to sort them by date.

One table holds games/, and another table holds the actions of these games/.

I am trying to get the recent events to show users 1) the actions taken on games that are publicly visible (published) 2) when a new game is created and published.

So, my actions table has

actionId, gameid, userid, actiontype, lastupdate

My games table has

gameid, startDate, createdby, published, lastupdate

I currently have a query like this (simplified for easy understanding I hope).

SELECT actionId, actions.gameid, userid, actiontype, actions.lastupdate
FROM actions 
JOIN
 (
   SELECT games.gameid, startDate, createdby, published, games.lastupdate 
   FROM games 
   WHERE 
    published=1 AND 
    lastupdate>today-2
 ) 
 publishedGames on actions.gameid=games.gameid 
WHERE 
 actions.type IN (0,4,5,6,7) AND 
 actions.lastupdate>games.lastupdate and 
 published=1 OR 
 games.lastupdate>today-2 AND 
 published=1

This query is looking for actions from published games where the action took place after the game was published. That pretty much takes care of the first thing that needs to be shown.

However, I also need to get the results of the

SELECT games.gameid, startDate, createdby, published, games.lastupdate
FROM games
WHERE
  published=1 AND 
  startDate>today-2

so I can include in the actions list, when a new game has been published.

When I run the query as I've got it written, I get all the actionids, and their gameids, but I don't get a row which shows the gameid when it was published.

I understand that it may be possible that I need to run two seperate queries, and then somehow merge the results afterword with php, but I'm completely lost on where to start with that as well.

Answer 1

select a.id, g.id, g.published ...
from actions a
join games g on on g.gameid = a.gameid
where g.published=1
and g.lastupdate > today - 2
and a.type IN (0,4,5,6,7)
and a.lastupdate > g.lastupdate