my leave table:
Field | Type | Null | Key
empID int(11) NO
EMPDETID int(11) NO
Leave_ID int(11) NO
LEAVEDATE datetime NO
FIRSTHALF tinyint(3) unsigned NO
SECONDHALF tinyint(3) unsigned NO
REQPENDING tinyint(4) YES
APPROVED tinyint(4) YES
REJECT tinyint(4) YES
my lev_definition table:
Field Type Null Key
LEVID int(11) NO PRI
LEVNAME varchar(20) YES
LEVSHORT varchar(5) YES
values in above table levid:1 -> levshort:el, levid:2 -> levshort:cl, levid:3->levshort:lop, levid:4-> levshort:sl,
my sanction table
Field Type Null Key
SID int(11) NO PRI
MUID int(11) YES MUL
EMPID int(11) YES MUL
now consider leave taken as(inserted values in table lev details)
empid:1 date:03/06/2010 levid:4 firsthalf:1 secondhalf:1 reqpending:0 approved:1 reject:0
empid:2 date:05/06/2010 levid:3 firsthalf:0 secondhalf:1 reqpending:0 approved:1 reject:0
empid:3 date:08/06/2010 levid:2 firsthalf:1 secondhalf:1 reqpending:0 approved:1 reject:0
empid:1 date:11/06/2010 levid:1 firsthalf:1 secondhalf:1 reqpending:0 approved:1 reject:0
empid:2 date:03/06/2010 levid:1 firsthalf:1 secondhalf:0 reqpending:0 approved:1 reject:0
now i want to get leave details leave type wise considering empid under sanction table
ex:muid='123' contains (empid=1, empid=2, empid=3)
like pl=1.5, cl=1, lop=0.5, sl=1
saction tables contain values like this
sid | MUID | empid
1 123 1
2 123 2
3 123 3
4 124 4
now below is my query
$query_lev="select x.levshort as LEAVESHORT, x.levname as LEAVENAME,
(select (count(*) + (select (count(*)/2) from lev_details where EMPID=a.empid and levid=a.Leave_ID and ((firsthalfyn=0 and secondhalfyn=1) or (firsthalfyn=1 and secondhalfyn=0)))) as res
from lev_details where empid=a.empid and firsthalfyn=1
and secondhalfyn=1 and levid=a.Leave_ID) as LEAVECOUNT
from leave a inner join lev_definition x on x.LEVID=a.Leave_ID
where (a.approved=1 and a.reject=0) and (a.empid) IN (select EMPID from sanction where muid='123'
a.LEAVEDATE
between STR_TO_DATE('".$from."','%d/%m/%Y')
and STR_TO_DATE('".$to."','%d/%m/%Y') GROUP BY x.levshort";
but this is fetching only 1 empid values
i need result
pl=1.5
cl=1
lop=0.5
sl=1