工资表的MY SQL查询

Question

I have 3 tables in MYSQL From 2 different database running in same server

and my expected Output is

MY attempt of query is

SELECT `biometric`.`Empid`,
,`name`,`location`,`basic`,`hra`,`conveyance`,`total salary`,sum(DISTINCT DATEDIFF(LEAST(`endDate`,'$monthEnd' ),GREATEST(`startdate`,'$monthStart'))+1) as leaves FROM `biometric`.`biometric`,`biometric`.`employee` JOIN `lms`.`leaves` WHERE `biometric`.`empid`= `employee`.`empid` AND `employee`.`empid` = `leaves`.`id` AND  
`startdate`<='$monthEnd' AND `endDate`>= '$monthStart' AND `leaves`.`status` = '3'
GROUP BY `Empid`

and here status =3 is approved leave and 1 is non approved leave. and my out put coming is

Thanks in Advance...

Answer 1

You can try below query, even I did not test it so if you get any error then you can create an sqlfiddle so that I can correct it.

Basic concept is that you need to use left join for lms.leaves table as you need all employee details even they did not took leave under status 3.

SELECT bmt.`Empid`,`name`,`location`,`basic`,`hra`,`conveyance`,`total salary`,
IFNULL(SUM(DISTINCT DATEDIFF(LEAST(`endDate`,'$monthEnd' ),GREATEST(`startdate`,'$monthStart'))+1),0) AS LEAVES 
FROM BM.`biometric` AS bmt
JOIN BM.`employee` AS emp ON bmt.`empid`= emp.`empid`
LEFT JOIN `lms`.`leaves` AS lvs ON emp.`empid` = lvs.`id` AND lvs.`status` = '3' AND `startdate`<='$monthEnd' AND `endDate`>= '$monthStart' 
GROUP BY bmt.`Empid`;