CODEWITHSUNDEEP
mysqldatabaseinner-join
Fofole
Fofole

Reputation: 3568

MYSQL tricky inner join error

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!

Upvotes: 0

Views: 117

Answers (1)

DRapp
DRapp

Reputation: 48179

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

Upvotes: 1

Related Questions