mysql - join two tables based on starting date and date

Question

I have the following table structure

Table hour_rate

CREATE TABLE `hour_rate` (
 `hour_rate_id` int(11) NOT NULL AUTO_INCREMENT,
 `hour_rate` decimal(8,2) NOT NULL,
 `from_date` date NOT NULL,
 `employee_id` int(11) NOT NULL,
 PRIMARY KEY (`hour_rate_id`),
 UNIQUE KEY `idx-unique-hour_rate-from_date-employee_id`
     (`from_date`,`employee_id`),
 KEY `idx-hour_rate-employee_id` (`employee_id`),
 CONSTRAINT `fk-hour_rate-employee_id` FOREIGN KEY (`employee_id`) 
     REFERENCES `employee` (`employee_id`) ON DELETE CASCADE ON UPDATE CASCADE,
) ENGINE=InnoDB;

Table employee_work

CREATE TABLE `employee_work` (
 `employee_work_id` int(11) NOT NULL AUTO_INCREMENT,
 `project_id` int(11) NOT NULL,
 `employee_id` int(11) NOT NULL,
 `date` date NOT NULL,
 `hours` int(11) NOT NULL,
 PRIMARY KEY (`employee_work_id`),
 UNIQUE KEY `idx-unique-employee_work-employee_id-date` 
     (`employee_id`,`date`),
 KEY `idx-employee_work-employee_id` (`employee_id`),
 CONSTRAINT `fk-employee_work-employee_id` FOREIGN KEY (`employee_id`) 
     REFERENCES `employee` (`employee_id`) ON DELETE CASCADE ON UPDATE CASCADE,
) ENGINE=InnoDB;

Table hour_rate contains records about employees and their hour rates (hour_rate) starting from date (from_date)

Table employee_work contains records with employees working hours per day

I want to select all records employee_work with appropriate hour rate from hour_rate based on date and from_date, so I can calculate the payment for the employee (hour_rate*hours)

For example i have the following records in hour_rate

+-----------+------------+-------------+
| hour_rate | from_date  | employee_id |
+-----------+------------+-------------+
|     11.00 | 2018-01-10 |           1 |
|     12.00 | 2018-01-14 |           1 |
|     13.00 | 2018-01-18 |           1 |
|      5.00 | 2018-01-01 |           1 |
|     10.00 | 2018-01-15 |           2 |
+-----------+------------+-------------+

and in employee_work

+-------------+------------+-------+
| employee_id | date       | hours |
+-------------+------------+-------+
|           1 | 2018-01-01 |     8 |
|           1 | 2018-01-02 |     8 |
|           1 | 2018-01-03 |     8 |
|           1 | 2018-01-04 |     8 |
|           1 | 2018-01-05 |     8 |
|           1 | 2018-01-08 |     8 |
|           1 | 2018-01-09 |     8 |
|           1 | 2018-01-10 |     8 |
|           1 | 2018-01-11 |     8 |
|           1 | 2018-01-12 |     8 |
|           1 | 2018-01-15 |     8 |
|           1 | 2018-01-16 |     8 |
|           1 | 2018-01-17 |     8 |
|           1 | 2018-01-18 |     8 |
|           1 | 2018-01-19 |     8 |
+-------------+------------+-------+

I expect to produce the following results

+-------------+------------+-------+--------+
| employee_id | date       | hours | payment|
+-------------+------------+-------+--------+
|           1 | 2018-01-01 |     8 |    40.0|
|           1 | 2018-01-02 |     8 |    40.0|
|           1 | 2018-01-03 |     8 |    40.0|
|           1 | 2018-01-04 |     8 |    40.0|
|           1 | 2018-01-05 |     8 |    40.0|
|           1 | 2018-01-08 |     8 |    40.0|
|           1 | 2018-01-09 |     8 |    40.0|
|           1 | 2018-01-10 |     8 |    88.0|
|           1 | 2018-01-11 |     8 |    88.0|
|           1 | 2018-01-12 |     8 |    88.0|
|           1 | 2018-01-15 |     8 |    96.0|
|           1 | 2018-01-16 |     8 |    96.0|
|           1 | 2018-01-17 |     8 |    96.0|
|           1 | 2018-01-18 |     8 |   104.0|
|           1 | 2018-01-19 |     8 |   104.0|
+-------------+------------+-------+--------+

Answer 1

You can do this by using select with 2 tables. The example for your data is below:

select ew.employee_id, ew.date, ew.hours, hr.employee_id, hr.from_date (hr.hour_rate * ew.hours) as payment

from employee_work ew, hour_rate hr 

where ew.employee_id = hr.employee_id

Answer 2

One method uses a correlated subquery to get the rate:

select ew.*,
       (ew.hours *
        (select hr.hour_rate
         from hour_rate hr
         where hr.employee_id = ew.employee_id and
               hr.from_date >= ew.date
         order by hr.from_date
         limit 1
        )
       ) as daily_pay
from employee_work ew;