Vallavan J Vallavan J - 24 days ago 16
MySQL Question

How to get the three values using join query

this the query:

How to get the value of three select query

Name,HOURS ENTERED',HOURS APPROVED,HOURS REJECTED

SELECT e.ENAME 'NAME', SUM(l.HOURS) 'HOURS ENTERED'
FROM labors l, emps e
WHERE l.EmpsID = e.EmpsID AND APPROVED = 'N' AND l.EmpsID = 'AGIUCL01'
AND l.DATE_WORK BETWEEN '2016-10-30' AND '2016-11-05'
union
(SELECT e.ENAME 'NAME', SUM(l.HOURS) 'HOURS APPROVED'
FROM labors l, emps e
WHERE l.EmpsID = e.EmpsID AND APPROVED = 'A' AND l.EmpsID = 'AGIUCL01'
AND l.DATE_WORK BETWEEN '2016-10-30' AND '2016-11-05')
union
SELECT e.ENAME 'NAME', SUM(l.HOURS) 'HOURS REJECTED'
FROM labors l,empse
WHERE l.EmpsID = e.EmpsID AND APPROVED = 'R'
AND l.EmpsID = 'AGIUCL01'
AND l.DATE_WORK BETWEEN '2016-10-30' AND '2016-11-05'

Answer

You can use conditionals in the SUM:

SELECT e.ENAME 'NAME',
  SUM(CASE WHEN APPROVED='N' THEN l.HOURS ELSE 0 END) 'HOURS ENTERED',
  SUM(CASE WHEN APPROVED='A' THEN l.HOURS ELSE 0 END) 'HOURS APPROVED',
  SUM(CASE WHEN APPROVED='R' THEN l.HOURS ELSE 0 END) 'HOURS REJECTED'
FROM labors l, emps e 
WHERE l.EmpsID = e.EmpsID
  AND l.EmpsID = 'AGIUCL01'
  AND l.DATE_WORK BETWEEN '2016-10-30' AND '2016-11-05'
Comments