Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
2  
You mean something like SELECT b.*, 'books' as table_name FROM books b? –  dezso Dec 3 '12 at 20:24
add comment

1 Answer

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.

share|improve this answer
1  
There is no need to put each select of a UNION into parentheses. –  a_horse_with_no_name Dec 3 '12 at 23:34
    
When I tested it, without the parentheses the LIMIT was only applying to the entire set. I was only getting 3 rows returned--the books. I'm running an older version (5.0.96), perhaps that behavior has changed. –  mdoyle Dec 4 '12 at 15:50
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.