title: Cross join, on multiple columns, without duplicates
body: <p>We have two tables with a <em>mostly</em> unique email, and a date where a transaction was sent (from one system) and received (in another system):</p>
  
  <pre><code>CREATE TABLE `alpha` (
    `id` int(11) NOT NULL,
    `email` varchar(255) NOT NULL,
    `date_sent` datetime NOT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  
  INSERT INTO `alpha`
  VALUES
  (12344,'loremipsum@example.com','2013-01-01 02:26:04'),
  (12345,'foobar@example.com','2013-01-01 04:39:16'),
  (12346,'foobar@example.com','2013-01-01 04:43:18');
  
  CREATE TABLE `bravo` (
    `id` int(11) NOT NULL,
    `email` varchar(60) DEFAULT NULL,
    `date_recvd` datetime DEFAULT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  
  INSERT INTO `bravo`
  VALUES
  (98764,'loremipsum@example.com','2013-01-01 03:29:12'),
  (98765,'foobar@example.com','2013-01-01 05:42:08'),
  (98766,'foobar@example.com','2013-01-01 05:46:08');
  </code></pre>
  
  <p>With a simple join on email and m/d/y of the date:</p>
  
  <pre><code>select a.id, a.date_sent, b.id, b.date_recvd
  from alpha a inner join bravo b
    on a.email = b.email and date_format(a.date_sent,'%m/%d/%Y') = date_format(b.date_recvd,'%m/%d/%Y')
  </code></pre>
  
  <p>We get every permutation of email+date:</p>
  
  <pre><code>| a.id  | a.date_sent         | b.id  | b.date_recvd        |
  +-------+---------------------+-------+---------------------+
  | 12344 | 2013-01-01 02:26:04 | 98764 | 2013-01-01 03:29:12 |
  | 12345 | 2013-01-01 04:39:16 | 98765 | 2013-01-01 05:42:08 |
  | 12346 | 2013-01-01 04:43:18 | 98765 | 2013-01-01 05:42:08 |
  | 12345 | 2013-01-01 04:39:16 | 98766 | 2013-01-01 05:46:08 |
  | 12346 | 2013-01-01 04:43:18 | 98766 | 2013-01-01 05:46:08 |
  </code></pre>
  
  <p>What we want is something more like this, where we join firstly on the email, and then only the dates in an order that they sort of line up:</p>
  
  <pre><code>| a.id  | a.date_sent         | b.id  | b.date_recvd        |
  +-------+---------------------+-------+---------------------+
  | 12344 | 2013-01-01 02:26:04 | 98764 | 2013-01-01 03:29:12 |
  | 12345 | 2013-01-01 04:39:16 | 98765 | 2013-01-01 05:42:08 |
  | 12346 | 2013-01-01 04:43:18 | 98766 | 2013-01-01 05:46:08 |
  </code></pre>
  
  <p>But I'm not even certain how to approach this?</p>
  
  <p><strong>Clarification:</strong> What we'd like to do is, emails being equal, eliminate the duplicates so that the date gaps are smallest.</p>