CODEWITHSUNDEEP
mysqlsqlselect
NestedWeb
NestedWeb

Reputation: 1737

Subtracting value based on the other table

--work--
day    hours 
 4       8
 5       9
 6       8


 --crew--
employee     day
   2          4
   3          4
   6          4  
   2          5
   3          5
   4          5

--break--
hours    day    employee
  1       4        2
  .5      5        3

Here is the sql query i have: 

 SELECT w.hours, w.hours-b.hours,  c.employee
 FROM work w
 LEFT JOIN crew c ON w.day = c.day
 LEFT JOIN break b ON w.day = b.day
 WHERE w.day = 4

this is subtracting 1 hour from every employee

i want the break.hours to be subtracted only if that employee has taken a break. if he hasn't, i simply want work.hours.

Upvotes: 2

Views: 93

Answers (1)

John Woo
John Woo

Reputation: 263723

COALESCE is all you need.

SELECT  a.employee,
        a.day,
        b.hours - COALESCE(c.hours,0) totalHours
FROM    crew a
        INNER JOIN work b
            ON a.day = b.day
        LEFT JOIN `break` c
            ON a.employee = c.employee AND
                a.day = c.day
-- WHERE    b.day = 4

Upvotes: 4

Related Questions