I am having a hard time configuring on how to join two tables, which the second one should be limit.
Below are the samples of my table.
This is employee table:
id EmpID Lastname Firstname Department
1 4 Joe Dylan Admin
2 5 Black Teddy Admin
This is the attendance table:
EmpID Date TimeIn TimeOut
4 2015-1-1 07:45 17:15
4 2015-1-2 08:15 15:00
4 2015-1-3 08:05 17:00
5 2015-1-1 08:00 16:00
5 2015-1-2 09:00 18:00
I want to combine the two tables by EmpID and show only one employee per page.
This is a sample of my expected result:
EmpID: 4
Dept: Admin
Name: Joe, Dylan
Date TimeIn TimeOut
2015-1-1 07:45 17:15
2015-1-2 08:15 15:00
2015-1-3 08:05 17:00
You just need to join and put where condition as:
$id=4;
$query="select employee.* ,attendance.* from (select * from employee where EmpID='".$id."') employee inner join attendance on employee.EmpID=attendance.EmpID";
Try
SELECT * from employee
LEFT JOIN attendance on attendance.EmpID=employee.EmpID
This will all attendance result of all employee; however if you want per page one employee record then trigger same query with PAgination;
SELECT * from employee
LEFT JOIN attendance on attendance.EmpID=employee.EmpID
WHERE employee.EmpID=per_page_emp_id
As per you comment that you need date requirement too then...
SELECT * from employee
LEFT JOIN attendance on attendance.EmpID=employee.EmpID
WHERE employee.EmpID=per_page_emp_id AND (
attendance.date BETWEEN state 2015-1-1 AND 2015-1-1