i've 5 tables known as personal,dailypay,bonuses,iou and financial loans am attempting to write a question which will generate payroll out of this table's...my code is

 select personal.name as NAME, 
   (sum(dailypay.pay) + bonuses) - (iou.amount + loans.monthly_due)) as SALARY 
    from personal 
      join dailypay on personal.eid = dailypay.eid 
      left join bonuses on personal.eid = bonuses.eid 
      left join iou on personal.eid = iou.eid 
      left join where dailypay.date = 'specified_date' 
                  and bonuses.date_approved = 'specified_date' 
                  and iou.date_approved = 'specified_date' 
                  and loans.date = month(now()

It returns the title and null salary values for employees that comes with records for either bonuses,iou and financial loans. But i wish to sum their dailypay, subtract/add breaks or additions return the values, in case of no record it will proceed using the summation with no deduction or subtraction.

You skipped something when pasting the code, as there's no join for that loans table. Also, you're while using table bonuses like a value, you'll need a area title also. I added some code for that join but for the area, but used ??? for names which are unknown in my experience.

Whenever you add or take away a null value to another thing, it makes sense null, this is exactly why you receive null as result when the values in the left-became a member of tables are missing. You should use ifnull(..., 0) to show a null value into zero.

You'll need a group by clause, otherwise it might summarize the salary for those persons.

Basically enable you to get right, there are several records within the dailypay table for every user, only one record per user within the other tables? For the reason that situation you will find the problem that you'll be joining another tables against each row within the dailypay, if you have 20 payment records for any user, it'll count the power 20 occasions. You should use an aggregate like max to obtain the value only one time.

You've put conditions for that left.became a member of tables within the where clause, but this can turn the joins into inner joins. You ought to have individuals conditions in every join clause.

select
  personal.name as NAME, 
  (sum(dailypay.pay) + ifnull(max(bonuses.???), 0)) - (ifnull(max(iou.amount), 0) + ifnull(max(loans.monthly_due), 0)) as SALARY 
from
  personal 
  inner join dailypay on personal.eid = dailypay.eid 
  left join bonuses on personal.eid = bonuses.eid and bonuses.date_approved = 'specified_date'
  left join iou on personal.eid = iou.eid and iou.date_approved = 'specified_date'
  left join loans on personal.??? = loans.??? and loans.date = month(now())
where
  dailypay.date = 'specified_date' 
group by
  personal.name

There appears to become an extranous left join prior to the where along with a missing closing bracket ) in month(now()

therefore it need to look like:

select personal.name as NAME, 
(sum(dailypay.pay) + bonuses) - (iou.amount + loans.monthly_due)) as SALARY 
from personal 
  join dailypay on personal.eid = dailypay.eid 
  left join bonuses on personal.eid = bonuses.eid 
  left join iou on personal.eid = iou.eid 
  where dailypay.date = 'specified_date' 
              and bonuses.date_approved = 'specified_date' 
              and iou.date_approved = 'specified_date' 
              and loans.date = month(now())