CODEWITHSUNDEEP
mysqlsql
Paul the Tutor
Paul the Tutor

Reputation: 45

SQL Joins to Get Monthly Total Wages from Three Tables

I need to get empolyees info from employees table, and their total wages from two different tables.

The SQL is approximately like this, but I don't really know how to use joins to do this:

I am not sure how to join these together properly. I don't want to see any of the employees that have not done either kind of work for the month, only those who have. I'm using mysql and will put the data in a table with php

If anyone could tell me how to do the "THIS MONTH" part that would be cool too. Just being lazy on that part, but figured while I was here...

Thanks for the help!

Upvotes: 0

Views: 1518

Answers (3)

Nitesh Kumar
Nitesh Kumar

Reputation: 1774

Try this query.

select 
    CONCAT(first_name, ' ', last_name) as employee_name,
    sum(case when t.this_date = 'this_month' then t.hours*t.pay else 0 end), 
    sum(case when n.this_date = 'this_month' then t.hours*t.pay else 0 end) 
from employees e 
    left join taxed_work t on e.id = t.employee_id
    left join nontaxed_work n on e.id = n.employee_id
group by (first_name, ' ', last_name)

Please replace the t.this_date and n.this_date fields with actual field names as I am not aware of the exact table structure. Also, replace the "this_month" value as per your need.

Upvotes: 0

John Woo
John Woo

Reputation: 263843

You can calculate their totals inside subquery.

SELECT  a.id ,
        CONCAT(first_name, ' ', last_name) FullName,
        b.totalTax,
        c.totalNonTax,
FROM    employees a
        LEFT JOIN 
        (
            SELECT employee_id, Sum(hours*pay) totalTax
            FROM taxed_work
            WHERE DATE_FORMAT(`date`,'%c') = DATE_FORMAT(GETDATE(),'%c')
            GROUP BY employee_id
        )  b ON b.employee_id = a.id
        LEFT JOIN 
        (
            SELECT employee_id, Sum(hours*pay) totalTax
            FROM nontaxed_work
            WHERE DATE_FORMAT(`date`,'%c') = DATE_FORMAT(GETDATE(),'%c')
            GROUP BY employee_id
        ) c ON c.employee_id = a.id

Upvotes: 0

Andomar
Andomar

Reputation: 238226

You could use correlated subqueries:

select  concat(first_name, ' ', last_name)
,       (
        select  sum(hours*pay)
        from    taxed_work tw
        where   tw.employee_id = e.id
                and year(tw.date) = year(now())
                and month(tw.date) = month(now())
        )
,       (
        select  sum(hours*pay)
        from    nontaxed_work ntw
        where   ntw.employee_id = e.id
                and year(ntw.date) = year(now())
                and month(ntw.date) = month(now())
        )
from    employees e

Upvotes: 2

Related Questions