CODEWITHSUNDEEP
mysqlleft-joininner-joingroupwise-maximum
Sebastian Farham
Sebastian Farham

Reputation: 825

How to limit one row per user id

I have this table called employeetimesheets:

empsheet_id|employee_id|timesheet_status|last_update

The table allows the manager to have access to all the employee time sheets. One employee can have several time sheets. I would like to display only the most recent entry per employee. I read in the manual I have to write a groupwise-maximum subquery and left join with inner join but I'm unsure how to go about it here.

So far this is the query I have:

$sqlempsheets="SELECT * FROM employeetimesheets JOIN employees ON employeetimesheets.employee_id=employees.employee_id WHERE employeetimesheets.timesheet_status='Pending Approval'";
$resultempsheets=mysqli_query($db,$sqlempsheets);

Upvotes: 0

Views: 346

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39467

Try this:

select *
from employeetimesheets t
join (
    select employee_id,
        max(empsheet_id) as empsheet_id
    from employeetimesheets
    group by employee_id
    ) t2 on t.employee_id = t2.employee_id
    and t.empsheet_id = t2.empsheet_id
join employees e on t.employee_id = e.employee_id
where t.timesheet_status = 'Pending Approval';

Or using left join:

select t.*, e.*
from employeetimesheets t
left join employeetimesheets t2 on t.employee_id = t2.employee_id
    and t.empsheet_id < t2.empsheet_id
join employees e on t.employee_id = e.employee_id
where t.timesheet_status = 'Pending Approval'
    and t2.employee_id is null;

Upvotes: 2

Related Questions