technoken technoken - 1 year ago 47
SQL Question

Selecting multiple tables and displaying them ordered by datetime column

I want to display the logs to recent activities page ordered by date. Now I was trying to execute this to my mysql

"SELECT * FROM tracking_log.editlog, tracking_log.deletelog, tracking_log.loginlog, tracking_log.logoutlog ORDER BY time ASC";


but it always says
Column 'time' in order clause is ambiguous


all of the tables have a time column, format by datetime (0000-00-00 00:00:00)

How am I going to fetch them ordered by time?

Thanks in advance!

Answer Source

By which table's time column you want to order?

Assuming you want to order the result set by tracking_log.editlog.time column then the query would look like below:

SELECT 
* 
FROM tracking_log.editlog, tracking_log.deletelog, 
     tracking_log.loginlog, tracking_log.logoutlog 
ORDER BY tracking_log.editlog.time ASC;

Just in case if all of the time columns in the respective table don't contain NOT NULL values at the same time then you need to use COALESCE I guess.

Query using COALESCE

SELECT 
* 
FROM tracking_log.editlog, tracking_log.deletelog, 
     tracking_log.loginlog, tracking_log.logoutlog 
ORDER BY 
COALESCE(tracking_log.editlog.time , tracking_log.deletelog.time, tracking_log.loginlog.time,tracking_log.logoutlog.time)   ASC;