title: Is it possible to left join two tables and have the right table supply each row no more than once?
body: <p>Given this table structure:</p>
  
  <pre><code>Table A
  ID    AGE    EDUCATION
  1     23     3
  2     25     6
  3     22     5
  
  Table B
  ID    AGE    EDUCATION
  1     26     4
  2     24     6
  3     21     3
  </code></pre>
  
  <p>I want to find all matches between the two tables where the age is within 2 and the education is within 2.  However, I do not want to select any row from TableB more than once.  Each row in B should be selected 0 or 1 times and each row in A should be selected one or more times (standard left join).</p>
  
  <pre><code>SELECT *
  FROM TableA as A LEFT JOIN TableB as B ON 
      abs(A.age - B.age) &lt;= 2 AND 
      abs(A.education - B.education) &lt;= 2
  
  A.ID    A.AGE    A.EDUCATION    B.ID    B.AGE   B.EDUCATION
  1       23       3              3       21      3
  2       25       6              1       26      4
  2       25       6              2       24      6
  3       22       5              2       24      6
  3       22       5              3       21      3
  </code></pre>
  
  <p>As you can see, the last two rows in the output have duplicated B.ID of 2 and 3 when compared to the entire result set.  I'd like those rows to return as a single null match with A.ID = 3 since they were both matched to previous A values.</p>
  
  <p>Desired output:</p>
  
  <p>(note that for A.ID = 3, there is no match in B because all rows in B have already been joined to rows in A.)</p>
  
  <pre><code>A.ID    A.AGE    A.EDUCATION    B.ID    B.AGE   B.EDUCATION
  1       23       3              3       21      3
  2       25       6              1       26      4
  2       25       6              2       24      6
  3       22       5              null    null    null
  </code></pre>
  
  <p>I can do this with a short program, but I'd like to solve the problem using a SQL query because it is not for me and I will not have the luxury of ever seeing the data or manipulating the environment.</p>
  
  <p>Any ideas? Thanks</p>