Jannat Jannat - 7 months ago 10
PHP Question

Join two table to get attendance and user name mysql

i have two tables employee_info and attendance:

CREATE TABLE IF NOT EXISTS `employee_info` (
`emp_id` varchar(20) NOT NULL,
`emp_name` varchar(30) NOT NULL,
`emp_contact` varchar(30) NOT NULL,
`com_address` varchar(30) NOT NULL,
`per_address` varchar(30) NOT NULL,
`com_phone` varchar(13) NOT NULL,
`com_email` varchar(20) NOT NULL,
`empid` int(20) NOT NULL,
`emp_company` varchar(20) NOT NULL,
`emp_branch` varchar(20) NOT NULL,
`emp_dept` varchar(20) NOT NULL,
`emp_designation` varchar(20) NOT NULL,
`emp_salary` varchar(30) NOT NULL,
`emp_type` varchar(10) NOT NULL,
`last_updated` date NOT NULL,
`active` int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `attendance` (
`id` int(11) NOT NULL,
`company` varchar(20) NOT NULL,
`branch` varchar(55) NOT NULL,
`dept` varchar(100) NOT NULL,
`employee_id` varchar(255) NOT NULL,
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`status` varchar(12) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;


i get total total present and absent count using this query:

select employee_id
,count(case when status =1 then 1 end) as absent_count
,count(case when status =2 then 1 end) as present_count
,count(distinct time) as Tot_count
from attendance where time between '2014-01-01' and '2016-04-12'
group by employee_id


how can i join these two table to get the emp_name???

Answer
select employee_id,
     count(case when status =1 then 1 end) as absent_count,
     count(case when status =2 then 1 end) as present_count,
     count(distinct time) as Tot_count 
from attendance  
     join employee_info on attendance.employee_id = employee_info.id 
where time between '2014-01-01' and '2016-04-12' 
group by employee_id
Comments