# Database Discussions > MySQL >  Help with complex query-urgent

## pskopje

HI Gurus,
A very weird issue and I cant figure this one out. Please see the below query.Its broken into 4 parts,part 1 is gets all the reported working hours, part 2 gets all the submitted working hours, part 3 gets all the approved working hours, part 4 gets all the manager approved hours. I did a left join since I want all the values for every employee even if they are zeros. I tried putting the whole thing together to be filtered by worked_date. I am not getting the correct results and records are being duplicated. PLEASE HELP!

Select B.WORKED_DATE,Q1.COLLABORATOR_ID, Q1.Name, Q1.RPT_HR, IFNULL(Q2.SUBMITTED_HR,0) AS SUBMITTED_HR, IFNULL(Q3.APP_PM_HR,0) as APP_PM_HR, IFNULL(Q4.APP_MGR_HR,0) as APP_MGR_HR
FROM

(SELECT A.COLLABORATOR_ID,B.WORKED_DATE, concat(A.FIRSTNAME,' ',A.LASTNAME) as Name,
IFNULL(SUM(B.WORKING_HOUR),0) as RPT_HR
FROM tbl_collaborator A LEFT JOIN tbl_time_sheet B
ON (A.COLLABORATOR_ID = B.COLLABORATOR_ID)
GROUP BY A.COLLABORATOR_ID, A.FIRSTNAME, A.LASTNAME,B.WORKED_DATE) AS Q1 LEFT JOIN

(SELECT A.COLLABORATOR_ID,B.WORKED_DATE, concat(A.FIRSTNAME,' ',A.LASTNAME) as Name,
IFNULL(SUM(B.WORKING_HOUR),0) as SUBMITTED_HR
FROM tbl_collaborator A LEFT JOIN tbl_time_sheet B
ON (A.COLLABORATOR_ID = B.COLLABORATOR_ID)
WHERE B.SUBMIT_FLAG = 1
GROUP BY A.COLLABORATOR_ID, A.FIRSTNAME, A.LASTNAME,B.WORKED_DATE) AS Q2
ON (Q1.COLLABORATOR_ID = Q2.COLLABORATOR_ID)
LEFT JOIN

(SELECT A.COLLABORATOR_ID,B.WORKED_DATE, concat(A.FIRSTNAME,' ',A.LASTNAME) as Name,
IFNULL(SUM(B.WORKING_HOUR),0) as APP_PM_HR
FROM tbl_collaborator A LEFT JOIN tbl_time_sheet B
ON (A.COLLABORATOR_ID = B.COLLABORATOR_ID)
WHERE B.APPROVER_ID > 0
GROUP BY A.COLLABORATOR_ID, A.FIRSTNAME, A.LASTNAME,B.WORKED_DATE) AS Q3
ON (Q1.COLLABORATOR_ID = Q3.COLLABORATOR_ID)
LEFT JOIN

(SELECT A.COLLABORATOR_ID,B.WORKED_DATE, concat(A.FIRSTNAME,' ',A.LASTNAME) as Name,
IFNULL(SUM(B.WORKING_HOUR),0) as APP_MGR_HR
FROM tbl_collaborator A LEFT JOIN tbl_time_sheet B
ON (A.COLLABORATOR_ID = B.COLLABORATOR_ID)
WHERE B.APPROVER_MANAGERID > 0
GROUP BY A.COLLABORATOR_ID, A.FIRSTNAME, A.LASTNAME,B.WORKED_DATE) AS Q4
ON (Q1.COLLABORATOR_ID = Q4.COLLABORATOR_ID)
WHERE Q1.WORKED_DATE = '2010-11-03'

----------


## Gil-Galad

Still need help on this one?
Willing to give hou a hand, but would love to have the table designs, preferably in SQL, to generate a test environment

----------

