Inner join Logic in mysql

Question

I am using Inner join where i want to combine 2 result to display in singel table.
I have created query to check latecoming and earlyleaving now i want both the output displayed as one singel table.
Query For lateComing:

    select k.EmpId,Min(k.DateofCheckin) as mindate,
    case 
    when min(time(DateofCheckin))<=@i then 'Ontime'
    when min(time(DateofCheckin)) between @i and @j then 'Late'
    when min(time(DateofCheckin)) between @j and @n then 'HalfDay'
    when DAYOFWEEK(Date(DateofCheckin)) =7 and  min(time(DateofCheckin)) >@j  then 'HalfDay'
    else 'Absent' end as LoginStatus
    from CheckInLogs k Join
    (select 
    @i:=time(r.LateComer) as LateComing,
    @j:=time(r.LateComingEndTime) as LateComingEnd,
    @m:=time(LunchStartTime),
    @n:=time(LunchEndTime),
    @o:=time(EarlyLeavingStartTime),
    @p:=time(EarlyLeavingEndTime), 
    e.EmpId
    FROM Employees e JOIN Profiles r ON e.LeaveProfile=r.ProfileName) h
    on k.EmpId=h.EmpId
    where k.DateofCheckin='in'  and  
    DAYOFWEEK(Date(k.DateofCheckin)) != 1 and
    k.BranchId=1 and date(DateofCheckin) between '2013-05-1' and '2013-05-29'
    group BY date(k.DateofCheckin),k.EmpId

Query For EarlyLeaving:

select 
    EmpId,Date(DateofCheckin) as LogoutDate,DateofCheckin,max(time(DateofCheckin)) as LogoutTime,
    case 
    when max(time(DateofCheckin))>=@p then 'Ontime'
    when DAYOFWEEK(Date(DateofCheckin)) =7 and  max(time(DateofCheckin)) < @m
    then 'HalfDay'
    when DAYOFWEEK(Date(DateofCheckin)) =7 and  max(time(DateofCheckin)) > @m  then 'Ontime'
    when max(time(DateofCheckin)) between @o and @p then 'EarlyLeaving'
    when max(time(DateofCheckin)) between @m  and @o then 'HalfDay'
    when max(time(DateofCheckin))<=@m then 'Absent'
    else 'Absent' end as LogoutStatus
    from CheckInLogs k1 Join
    (select 
    @i:=time(r.LateComingStartTime) as LateComing,
    @j:=time(r.LateComingEndTime) as LateComingEnd,
    @m:=time(LunchStartTime),
    @n:=time(LunchEndTime),
    @o:=time(EarlyLeavingStartTime),
    @p:=time(EarlyLeavingEndTime), 
    e.EmpId
    FROM Employees e JOIN Profiles r ON e.LeaveProfile=r.ProfileName) h1
    on k1.EmpId=h1.EmpId
    where k1.DateofCheckin='Out' 
    and k1.BranchId=1
    and DAYOFWEEK(Date(k1.DateofCheckin)) != 1 and  
    date(k1.DateofCheckin) between '2013-06-1' and '2013-06-29'
    group BY date(k1.DateofCheckin),k1.EmpId

Answer 1

Below is an example of how i'd approach the query, this was a quick copy and past job so you'd have to fix syntax errors etc. The main difference is that you only need to get the employee details once at the start of the query and add a common field between the two current queries (other than employee) to join on. In this case I have used logoutdate

select *
from (select 
    @i:=time(r.LateComer) as LateComing,
    @j:=time(r.LateComingEndTime) as LateComingEnd,
    @m:=time(LunchStartTime),
    @n:=time(LunchEndTime),
    @o:=time(EarlyLeavingStartTime),
    @p:=time(EarlyLeavingEndTime), 
    e.EmpId
FROM Employees e JOIN Profiles r ON e.LeaveProfile=r.ProfileName) h
JOIN (select 
        EmpId, Date(DateofCheckin) as LogoutDate,
        DateofCheckin,max(time(DateofCheckin)) as LogoutTime,
        case 
            when max(time(DateofCheckin))>=@p then 'Ontime'
            when DAYOFWEEK(Date(DateofCheckin)) =7 and  max(time(DateofCheckin)) < @m
                then 'HalfDay'
            when DAYOFWEEK(Date(DateofCheckin)) =7 and  max(time(DateofCheckin)) > @m                  then 'Ontime'
            when max(time(DateofCheckin)) between @o and @p then 'EarlyLeaving'
            when max(time(DateofCheckin)) between @m  and @o then 'HalfDay'
            when max(time(DateofCheckin))<=@m then 'Absent'
        else 'Absent' end as LogoutStatus
      from CheckInLogs k1    
      where k1.DateofCheckin='Out' 
      and k1.BranchId=1
      and DAYOFWEEK(Date(k1.DateofCheckin)) != 1
      group BY date(k1.DateofCheckin), k1.EmpId) out
ON out.empid = h.empid
JOIN (select k.EmpId, Date(DateofCheckin) as LogoutDate,
                Min(k.DateofCheckin) as mindate,
                case 
                    when min(time(DateofCheckin))<=@i then 'Ontime'
                    when min(time(DateofCheckin)) between @i and @j then 'Late'
                    when min(time(DateofCheckin)) between @j and @n then 'HalfDay'
                    when DAYOFWEEK(Date(DateofCheckin)) =7 
                        and min(time(DateofCheckin)) >@j  then 'HalfDay'
                    else 'Absent' end as LoginStatus
      from CheckInLogs k Join
        where k.DateofCheckin='in'  and  
        DAYOFWEEK(Date(k.DateofCheckin)) != 1 and
        group BY date(k.DateofCheckin),k.EmpId) in
ON in.empid = out.empid
AND in.LogoutDate = out.LogoutDate

I would also consider moving the case and group bys to the top level query.