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
EMPIDint(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