从两个表中选择并按时间戳排序

Question

I have two tables (stories and status), each with these common fields

id (unsigned int - auto increment)
creator (unsigned int)
message (varchar)
timestamp (unsigned int)

When I display these tables on my webpage, I want to use one query to select from both tables in order of timestamp, but display them differently.

Like (in order of timestamp):

SELECT * FROM `stories`, `statuses` WHERE `creator` = 1 ORDER BY `timestamp` DESC LIMIT 0, 10

Row 1: id, creator, message, timestamp, type ("status")
Row 2: id, creator, message, timestamp, type ("story")
Row 3: id, creator, message, timestamp, type ("status")
Row 4: id, creator, message, timestamp, type ("status")
Row 5: etc...

I need the type field to display each row differently on my webpage. And this is only the simple form of each table; they are actually much more complex, but I could transfer the answer from here over to my current query.

Thanks!

Answer 1

You can use UNION operator to combine the result-set of two or more SELECT statements.

SELECT a.id, a.creator, a.message, a.timestamp, 'story' as table_type
FROM stories a
UNION
SELECT b.id, b.creator, b.message, b.timestamp, 'status' as table_type
FROM statuses b
WHERE ( a.creator = 1 ) OR (b.creator = 1)
ORDER BY 'timestamp' DESC LIMIT 0, 10;

Please note that the UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL.

SELECT a.id, a.creator, a.message, a.timestamp, 'story' as table_type
FROM stories a
UNION ALL
SELECT b.id, b.creator, b.message, b.timestamp, 'status' as table_type
FROM statuses b
WHERE ( a.creator = 1 ) OR (b.creator = 1)
ORDER BY 'timestamp' DESC LIMIT 0, 10;

I hope this could help you.