CareerCup

Table :-

1. Employee ( id, name, email, password, manager_id )
2. Role_mapping ( id, employee_id, role_id )
3. Role ( id, role_name, city_name)
3. Attendance (id, employee_id, date, swap_in_time, swap_out_time)

for employee :-
select at.swap_in_time, at.swap_out_time from Attendance at inner join Employee emp on emp.id = at.employee_id where emp.email = "abc@example.com" and at.data = now();

1. employee ( id, name, email, password, manager_id, role_id)
2. role (id, name, city_name)
3. attendance( id, employee_id, swap_in_time, swap_out_time, date).

for employee :-
select at.swap_in_time, at.swap_out_time from attendance at inner join employee emp on emp.id = at.employee_id where emp.email = "abc@example.com" and at.date = now();

Pls correct if anything missed ..
Tables/Schemas: Employee(emp_Id, name, email, password,manager_id,role_id)
roles(role_id, role, city_name)
attendence(id,emp_Id,role_id,date,swap_in_time, swap_out_time)
Query :
SELECT a.swap_in_time,a.swap_out_time
FROM attendence a inner join employee e
inner join roles r
ON e.emp_Id = a.emp_Id and e.role_id = e.role_id
where r.role_name IN ('manager','employee',HR') ORDER BY swap_in_time,swap_in_time DESC;

UPDATE attendence a SET a.swap_in_time=<>, a.swap_in_time=<> where role_id IN (select role_id from roles where role IN ('manager','employee')

abc