I have 3 tables, all with some kind of articles: articles, columns and reports. All tables have a column called date, which contains the Unix timestamp when the record was added.
Now, I want to get the 50 most recently added records of the articles, columns and reports table and list them on a webpage. Because an article and a column can have the same unique ID, it is also necessary to know which result came from which table, for linking to the complete article (www.webpage.com/article/12 or www.webpage.com/column/12 for instance).
What is the best way to achieve this?
Frankly, the best way is probably to pull the elements into different variables. Anyway, here's an alternative if you want to do it all at once:
SELECT a.date, a.title, 'ARTICLE' category
FROM articles a
WHERE date > :date
UNION ALL
SELECT date, title, 'COLUMN' category
FROM columns
WHERE date > :date
UNION ALL
SELECT date, title, 'REPORT' category
FROM reports
WHERE date > :date
Something like that, anyway. The idea is that you can include a flag referencing the source table in each sub-select statement.