CODEWITHSUNDEEP
mysqldatabasejoin
code4
code4

Reputation: 35

Issues with a MySQL JOIN statement

i have 5 tables called personal,dailypay,bonuses,iou and loans am trying to write a query that will generate payroll from 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 name and null salary values for staffs that does have records for either bonuses,iou and loans. But i want to sum their dailypay, deduct/add deductions or additions return the values, in the event of no record it should proceed with the summation without any deduction or subtraction.

Upvotes: 0

Views: 60

Answers (2)

Guffa
Guffa

Reputation: 700690

You missed something when pasting the code, as there is no join for the loans table. Also, you are using the table bonuses as a value, you need a field name also. I added some code for the join and for the field, but used ??? for names that are unknown to me.

When you add or subtract a null value to something else, the result is null, that's why you get null as result when any of the values from the left-joined tables are missing. You can use ifnull(..., 0) to turn a null value into zero.

You need a group by clause, otherwise it would sum up the salary for all persons.

If I get you right, you have several records in the dailypay table for each user, but only one record per user in the other tables? In that case you have the problem that you will be joining the other tables against each row in the dailypay, so if you have 20 payment records for a user, it will count the bonus 20 times. You can use an aggregate like max to get the value only once.

You have put conditions for the left.joined tables in the where clause, but this will turn the joins into inner joins. You should have those conditions in each 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

Upvotes: 1

dgw
dgw

Reputation: 13666

There seems to be an extranous left join before the where and a missing closing bracket ) in month(now()

so it should 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())

Upvotes: 0

Related Questions