Join 2 MySQL tables on multiple values

Question

I am using this example database where I ran the following queries from two different tables

mysql> select * from salaries where emp_no=10017;
+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
|  10017 |  71380 | 1993-08-03 | 1994-08-03 |
|  10017 |  75538 | 1994-08-03 | 1995-08-03 |
|  10017 |  79510 | 1995-08-03 | 1996-08-02 |
|  10017 |  82163 | 1996-08-02 | 1997-08-02 |
|  10017 |  86157 | 1997-08-02 | 1998-08-02 |
|  10017 |  89619 | 1998-08-02 | 1999-08-02 |
|  10017 |  91985 | 1999-08-02 | 2000-08-01 |
|  10017 |  96122 | 2000-08-01 | 2001-08-01 |
|  10017 |  98522 | 2001-08-01 | 2002-08-01 |
|  10017 |  99651 | 2002-08-01 | 9999-01-01 |
+--------+--------+------------+------------+
10 rows in set (0.01 sec)

mysql> select * from titles where emp_no=10017;
+--------+--------------+------------+------------+
| emp_no | title        | from_date  | to_date    |
+--------+--------------+------------+------------+
|  10017 | Senior Staff | 2000-08-03 | 9999-01-01 |
|  10017 | Staff        | 1993-08-03 | 2000-08-03 |
+--------+--------------+------------+------------+
2 rows in set (0.01 sec)

Now what I want is to join these two tables such that I get the following table

emp_no  title           salary  years
10017   Staff           71380   1993
10017   Staff           75538   1994
10017   Staff           79510   1995
10017   Staff           82163   1996
10017   Staff           86157   1997
10017   Staff           89619   1998
10017   Staff           91985   1999
10017   Staff           96122   2000
10017   Staff           98522   2001
10017   Senior Staff    99651   2002

But I try to join these two tables I am getting duplicate values like the following which are logical because 2 values in table 2 are relating with 10 values in table 1. I also tried to join on multiple columns using from_date but you can see there is a slight difference in dates so I cannot do that.

mysql> select salaries.emp_no, titles.title, salaries.salary, salaries.from_date
from titles
inner join salaries
on salaries.emp_no = titles.emp_no
where salaries.emp_no=10017; 

emp_no  title           salary  from_date
10017   Senior Staff    71380   1993-08-03
10017   Senior Staff    75538   1994-08-03
10017   Senior Staff    79510   1995-08-03
10017   Senior Staff    82163   1996-08-02
10017   Senior Staff    86157   1997-08-02
10017   Senior Staff    89619   1998-08-02
10017   Senior Staff    91985   1999-08-02
10017   Senior Staff    96122   2000-08-01
10017   Senior Staff    98522   2001-08-01
10017   Senior Staff    99651   2002-08-01
10017   Staff           71380   1993-08-03
10017   Staff           75538   1994-08-03
10017   Staff           79510   1995-08-03
10017   Staff           82163   1996-08-02
10017   Staff           86157   1997-08-02
10017   Staff           89619   1998-08-02
10017   Staff           91985   1999-08-02
10017   Staff           96122   2000-08-01
10017   Staff           98522   2001-08-01
10017   Staff           99651   2002-08-01

My question: How can I get the required result knowing that there is no other way to link them ? Or is there something which I am forgetting ?

Answer 1

Check if dates overlap:

Determine Whether Two Date Ranges Overlap

select salaries.emp_no, titles.title, salaries.salary, salaries.from_date
from titles
inner join salaries
   on salaries.emp_no = titles.emp_no
  AND salaries.from_date <= titles.to_date 
  AND salaries.to_date >= titles.from_date
where salaries.emp_no=10017; 

EDIT: probably just a contain check will do the work

  AND salaries.from_date >= titles.from_date
  AND salaries.to_date < titles.to_date