MYSQL tricky inner join error

Question

I am trying to obtain first person in from a day(marked with user_log.userlog_log_type=1) and last person out(log_type=2) on all days from my user_log table. In users I have user_id pk and user_name and in user_log I have userlog_user_id FK for user_id in first table.

Now I managed to get the first entry for each day and the last entry for each day. But when I use inner join to get the table I want(day,firstIn,lastOut) , I get an error and can't seem to find what I do wrong:

If anyone can help I would really appreciate it. Thanks a lot!

DRapp · Accepted Answer · 2012-03-06 13:13:05Z

It looks like you are going through a lot to get a little... I would start with a single query to get on a per day basis the min and max person, then from that. I would want an index on the user log time stamp...

select LogInOut.LogActivity, LogInOut.JustDay, LogInOut.What_Time, ULMain.UserLog_User_ID, U1.user_name from ( select day( UL.UserLog_Timestamp ) as JustDay, max( UL.UserLog_Log_type ) as LogActivity, min( UL.userlog_timestamp ) as What_Time from UserLog UL where UL.UserLog_Log_Type = 1 group by day( UL.UserLog_Timestamp ) UNION select day( UL.UserLog_Timestamp ) as JustDay, max( UL.UserLog_Log_type ) as LogActivity, max( UL.userlog_timestamp ) as What_Time from UserLog UL where UL.UserLog_Log_Type = 2 group by day( UL.UserLog_Timestamp ) ) LogInOut JOIN UserLog ULMain on LogInOut.What_Time = ULMain.UserLog_Timestamp AND LogInOut.LogActivity = ULMain.UsrLog_Log_Type JOIN Users U1 on ULMain.UserLog_User_ID = U1.User_ID order by LogInOut.JustDay, LogInOut.LogActivity

would create something like

LogActivity JustDay What_Time UserLog_User_ID user_name 1 Mar 1 7:56am 123 Bill Board 2 Mar 1 6:23pm 431 Eilean Dover 1 Mar 2 7:02am 98 Crystal Clear 2 Mar 2 6:47pm 221 Ben Dover

Now, if you want this rolled-up to a single row (cross-tab) so one row shows the day, who in first, who out last, I would wrap change the top portion and add a group by something like

select LogInOut.JustDay, MIN( LogInOut.What_Time ) as EarlyLogin, MAX( IF( LogInOut.LogActivity = 1, U1.User_Name, ' ' )) as EarlyUser, MAX( LogInOut.What_Time ) as LastLogOut MAX( IF( LogInOut.LogActivity = 2, U1.User_Name, ' ' )) as LastUser from (exact rest of from / join / order by clause) group by LogInOut.JustDay JustDay EarlyLogin EarlyUser LastLogOut LastUser Mar 1 7:56am Bill Board 6:23pm Eilean Dover Mar 2 7:02am Crystal Clear 6:47pm Ben Dover

As I stated in my edited question, I can only use those 2 tables. +1 though.
@Fofole, I was only using the two tables you supplied... the "Alias" references were the internal SQL temporary result sets, no "other" tables were used.

Collectives™ on Stack Overflow

MYSQL tricky inner join error

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related