How do I return data from multiple MySQL tables in date order?

Question

I have another MySQL problem. Again, with the complex queries I'm in over my head.

On my website I currently display the last 5 reviewed games on the main page. Now, it's been ticking over a while and I've decided to add news items as well to the same page.

But, I want to change the system that I have into one that displays news entries as well as reviews - showing the last entry that was posted in either category. Both tables are completely unrelated (news, review).

The following columns are what I want to be returned:

news.newsTitle
news.newsBody
news.postedOn (timestamp)
review.postedOn (timestamp)
review.reviewSummary
review.ourScore
game.gameName
game.gameImage

The game table is connected to the review table with gameID, but neither of these tables are associated with the news table.

The current query is this:

SELECT news.newsTitle, news.newsBody, DATE_FORMAT( review.postedOn,  '%M %d, %Y' ) AS reviewPosted, DATE_FORMAT( news.postedOn,  '%M %d, %Y' ) AS newsPosted, game.gameID, gameName, gameImage, review.reviewSummary, review.ourScore
FROM game
LEFT JOIN news ON news.newsID = news.newsID
LEFT JOIN review ON game.gameID = review.gameID
WHERE game.isPublished =  'y'
ORDER BY game.gameID DESC 
LIMIT 0 , 5

But all this does is display the news item 5 times along with the game information.

Any assistance would be much appreciated.

Answer 1

You need to investigate UNION - create two queries that output the same number of (and equivalent) columns, join the results using UNION then order by date and limit results to the top 5.

Using the above, you could return the newest top 5, regardless of their mix between news / reviews - is that what you mean?

The following should return the desired data:

(SELECT news.newsTitle AS title, news.newsBody AS body, 
DATE_FORMAT( news.postedOn,  '%M %d, %Y' ) AS posted,
news.postedOn, null AS image, null AS score
FROM news)
UNION ALL
(SELECT game.gameName as title, review.reviewSummary AS body,
DATE_FORMAT( review.postedOn,  '%M %d, %Y' ) AS posted,
review.postedOn, game.gameImage AS image, review.ourScore AS score
FROM game LEFT JOIN review ON game.gameID = review.gameID 
WHERE game.isPublished = 'y')    
ORDER BY postedOn DESC
LIMIT 0,5

Answer 2

Why do you want to pull two completely unrelated pieces of data with the same query? You could do some acrobatics by generating a rowid for each result from reviews and from news, then joining the results on the rowids; or by selecting like columns from both and using UNION as BrynJ suggested. But all of this seems totally unnecessary when you can just issue two queries.

Do these things belong together?

If you were writing this using objects in memory, not a database -- would you have some Collection that included both, or two separate collections that contained them separately?

Update I got the answers to my questions in the comments; now I understand what you are trying to do. There are two ways I've seen this type of thing done: one is to select the most recent data from all sources (comments, reviews, news, uploaded photos, etc), sort them by date, and get the top X of those on the client. This has the drawback of the number of queries increasing in proportion with the number of your data sources, but it has the benefit of being very flexible in terms of the presentation layer -- you can select different columns from different objects, format them as you desire, and do whatever you need to on the client side (client in this case being your software, not the web client).

The other is to enforce a consistent set of columns you need to represent any object, which can possibly be null -- usually stuff like "type, title, body, date, img_id" or something of the sort -- and use a union, as suggested by the other poster. This has the benefit of allowing you to use a single query, making your software less complicated; it has the drawbacks of giving you less flexibility when it comes to other types of things you might want to stick in the feed, and of possibly overloading the database (that part depends on how big a bottleneck the DB is in your case).

Answer 3

Okay, I'm halfway there. The problem was the date formatting sort, by selecting the timestamp as a dummy MySQL now returns the correct result set. Phew!

(
SELECT news.newsTitle AS title, news.newsBody AS body, news.postedOn AS dummy, DATE_FORMAT( news.postedOn,  '%M %d, %Y' ) AS posted
FROM news
)
UNION ALL (

SELECT game.gameName AS title, review.reviewSummary AS body, review.postedOn AS dummy, DATE_FORMAT( review.postedOn,  '%M %d, %Y' ) AS posted
FROM game
LEFT JOIN review ON game.gameID = review.gameID
WHERE game.isPublished =  'y'
)
ORDER BY dummy DESC 
LIMIT 0 , 5

Can I force MySQL to return the rest of the data (the game score and game image) for the review rows and null if it's a news row?

I realise this may not be possible but if a quick hack will do it, so the better.