combining 2 tables in mysql

Question

Here is what I am looking to do but I have my doubts if its possible or not!

I have 2 tables, one is called users and the other is answers. I need to combine the results into a single statement, and order the table by time. the tricky part is that I have time on both tables and I need to take both time fields into account for ordering the results.

This is my unsuccessful attempt to come up with the query:

SELECT * FROM ( SELECT id, username, first_name, last_name, time FROM users
UNION ALL
  SELECT id, answer, pid, uid, time FROM answers ) as AB
ORDER BY `AB.time`

UPDATE

I have come up with the following SQL Query:

 SELECT username AS user, '' as page , '' as content , time FROM users 
UNION ALL
  SELECT (SELECT username FROM users WHERE `id`=`uid`) AS user, pid AS page, answer AS content, time FROM answers 
ORDER BY `time` DESC

its what I am looking for but for some reason the user field in the second SELECT query is showing as null! any idea why?

Answer 1

 SELECT id, username, first_name, last_name, time FROM users
UNION ALL
  SELECT id, answer, pid, uid, time FROM answers 
ORDER BY 'time`

solution to have different column name for each table

SELECT id         AS t1_id, 
       username   AS t1_username, 
       first_name AS t1_first_name, 
       last_name  AS t1_last_name, 
       NULL       ast2_id, 
       NULL       AS t2_answer, 
       NULL       AS t2_pid, 
       NULL       AS t2_uid, 
       time 
FROM   users 
UNION ALL 
SELECT NULL   AS t1_id, 
       NULL   AS t1_username, 
       NULL   AS t1_first_name, 
       NULL   AS t1_last_name, 
       id     ast2_id, 
       answer AS t2_answer, 
       pid    AS t2_pid, 
       uid    AS t2_uid, 
       time 
FROM   answers 
ORDER  BY time 

Answer 2

If users.id and answers.uid are supposed to be the same then this should work:

SELECT u.*, a.* FROM users AS u
    JOIN answers AS a ON a.uid = u.id
    ORDER BY a.time