title: Complex SQL query for correspondance between two tables
body: <p>I'm still working on the same project as when I asked <a href="https://stackoverflow.com/questions/33607479/sql-select-inner-join-one-by-one">my previous question on Stack Overflow</a>. My SQL tables are fully described in that question, and I would ask you to read this to understand my new problem.</p>
  
  <p>The difference now is that the two tables <em>trades</em> and <em>events</em> are <strong>no longer synchronized</strong> : now, the time doesn't exactly corresponds between the two tables. However, <strong>I know there is still a one-to-one correspondance between the two tables</strong>, meaning that <strong>each trade has a corresponding event</strong>, but some <em>events</em> doesn't correspond to a <em>trade</em>.</p>
  
  <p><em>Trades</em> :</p>
  
  <pre><code>  id |   time    |  price  | volume |   foo
  -----+-----------+---------+--------+-------
   201 | 32400.524 |      53 |   2085 |   xxx
   202 | 32400.530 |      53 |   1162 |   xxx
   203 | 32400.531 |   52.99 |     50 |   xxx
   204 | 32401.532 |   52.91 |   3119 |   xxx
   205 | 32402.437 |   52.91 |   3119 |   xxx
   206 | 32402.832 |   52.91 |   3119 |   xxx
   207 | 32403.255 |   52.91 |   3119 |   xxx
   208 | 32404.242 |   52.92 |   3220 |   xxx
   209 | 32405.823 |   52.92 |   3220 |   xxx
   210 | 32406.839 |   52.92 |   3220 |   xxx
  </code></pre>
  
  <p><em>Events</em> :</p>
  
  <pre><code>   id |   time    |  price  | volume |  bar 
  -----+-----------+---------+--------+------
   328 | 32399.345 |   52.91 |   3119 |  yyy
   329 | 32400.964 |   52.91 |   3119 |  yyy
   330 | 32401.194 |   52.91 |   3119 |  yyy
   331 | 32401.746 |   52.91 |   3119 |  yyy
   332 | 32401.823 |   52.91 |   3119 |  yyy
   333 | 32402.534 |   52.91 |   3119 |  yyy
   334 | 32402.876 |   52.92 |   3220 |  yyy
   335 | 32403.839 |   52.92 |   3220 |  yyy
   336 | 32404.634 |   52.92 |   3220 |  yyy
   337 | 32405.234 |   52.91 |   2501 |  yyy
  </code></pre>
  
  <p>What I want is making the correspondance between the two tables by <strong>minimizing the difference of time between the trade and the event</strong>. It makes sense : if there are several events which correspond to a trade in volume and price, we must take the event which is the least "far in time" from the trade.</p>
  
  <p>I have tried to do the following thing :</p>
  
  <pre><code>SELECT 
      t.*,
     (SELECT e.id
          FROM events o
          WHERE e.price = t.price
          AND e.volume = t.volume
          ORDER BY ABS(o.time - t.time)
          LIMIT 1
      ) as most_probable_corresponding_event_id
  FROM trades t
  ORDER BY t.time;
  </code></pre>
  
  <p>But the problem is this query <strong>doesn't give unique correspondance</strong> : a same event e can be choose for different trades t1 and t2, if this event is the nearest from both trades t1 and t2. What I want is making an exclusive correspondance.</p>
  
  <p>Thank you for your help.</p>
  
  <p>EDIT : </p>
  
  <p>The output I expect for the example datas would be :</p>
  
  <pre><code>   trade_id | order_id |  price  | volume |  bar |   foo 
   -----------+----------+---------+--------+------+-------
        204   |   331    |   52.91 |   3119 |  xxx |   yyy
        205   |   333    |   52.91 |   3119 |  xxx |   yyy
        206   |   334    |   52.91 |   3119 |  xxx |   yyy
        207   |   335    |   52.92 |   3220 |  xxx |   yyy
        208   |   336    |   52.92 |   3220 |  xxx |   yyy
        209   |   337    |   52.92 |   3220 |  xxx |   yyy
  </code></pre>