technoken technoken - 4 months ago 7
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

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;