Getting a “type” column from each table in query

Question

Let's assume I have 3 tables: videos, books, albums and I would like a query that gives me the 3 latest entries in each table, along with a column "media_type" valued Video, Book, or Album, accordingly.

I know how to get the 3 latest entries from each table, but how do I create the temporary column?

Answer 1

Perhaps this is what you are looking for:

(SELECT id, name, 'Book' AS media_type
FROM books
LIMIT 0,3) 

UNION ALL 

(SELECT id, name, 'Video' AS media_type
FROM videos
LIMIT 0,3)

UNION ALL

(SELECT id, name, 'Album' AS media_type
FROM albums
LIMIT 0,3);

I don't know if you need any particular ordering or not. According to the docs, UNION produces an unordered set of rows. You should be able to add an ORDER BY after the last SELECT, outside the parentheses.