title: Join where 2 time periods intersect
body: <p>We have 2 tables, and are trying to figure out how to do a cross join where 2 time periods intersect.</p>
  
  <p>The first table (<code>employment</code>) contains 4 columns:</p>
  
  <pre><code>EmployerID, UserID, StartDate, EndDate
  </code></pre>
  
  <p>The second table (<code>status_history</code>) also contains 4 columns:</p>
  
  <pre><code>UserID, Status, StartDate, EndDate
  </code></pre>
  
  <p><code>employment</code> table contains records showing which "job" each user was associated with, and for how long (StartDate and EndDate). Similarly, <code>status_history</code> contains records showing whether the user was Active / Inactive (employed, or unemployed) - also with StartDate and EndDate.</p>
  
  <p>We are trying to build a view that would create a proper "cross join" between the two tables.</p>
  
  <pre><code>EmployerID, UserID, Status, StartDate, EndDate
  </code></pre>
  
  <p>I tried creating an SQL Fiddle, but for some reason, I'm getting an error from them. So, I have provided the Schema below:</p>
  
  <pre><code>CREATE TABLE employment
      (`EmployerID` int, `UserID` int, `StartDate` date, `EndDate` date);
  
  CREATE TABLE status_history
      (`UserID` int, `Status` varchar(10), `StartDate` date, `EndDate` date);
  
  INSERT INTO employment
      (`EmployerID`, `UserID`, `StartDate`, `EndDate`)
  VALUES
      (123, 111, '2017-01-01', '2017-03-04'),
      (345, 111, '2017-03-04', '2017-03-07'),
      (567, 111, '2017-03-07', '2017-04-10'),
      (789, 111, '2017-04-10', NULL)
  ;
  
  INSERT INTO status_history
      (`UserID`, `Status`, `StartDate`, `EndDate`)
  VALUES
      (111, 'Active', '2017-01-01', '2017-02-17'),
      (111, 'Inactive', '2017-02-17', '2017-03-02'),
      (111, 'Active', '2017-03-02', '2017-03-09'),
      (111, 'Inactive', '2017-03-09', NULL),
  ;
  </code></pre>
  
  <p>Based on the data, I want to retrieve following rows:</p>
  
  <pre><code>+------------+---------+-----------+-------------+-------------+
  | EmployerID |  UserID |   Status  |  StartDate  |   EndDate   |
  +------------+---------+-----------+-------------+-------------+
  |        123 |     111 |  Active   |  2017-01-01 |  2017-02-17 |
  |        123 |     111 |  Inactive |  2017-02-17 |  2017-03-02 |
  |        123 |     111 |  Active   |  2017-03-02 |  2017-03-04 |
  |        345 |     111 |  Active   |  2017-03-04 |  2017-03-07 |
  |        567 |     111 |  Active   |  2017-03-07 |  2017-03-09 |
  |        567 |     111 |  Inactive |  2017-03-09 |  2017-04-10 |
  |        789 |     111 |  Inactive |  2017-04-10 |  NULL       |
  +------------+---------+-----------+-------------+-------------+
  </code></pre>
  
  <p>Any help shall be appreciated!</p>