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!
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.
You can use a JOIN to do this combined with SELECT column AS 'some name' syntax.
For example...
SELECT statuses.somefield AS 'some name' FROM `stories` JOIN(`statuses`) ON (status.id = stories.id) WHERE stories.creator = 1 ORDER BY stories.timestamp DESC LIMIT 0, 10