Reputation: 2896
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?
Upvotes: 0
Views: 65
Reputation: 20775
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
Upvotes: 2
Reputation: 1871
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
Upvotes: 1