title: SQL JOIN one-to-many but don't repeat previous joins
body: <p>I need to create a join between two tables of companies on the <strong>COMPANY_NAME</strong> column (as there are no other common keys to join on). Subsidiary companies with the same name can exist in the tables.</p>
  <p>The tables with the relevant data look like this:</p>
  <p><strong>Table A</strong></p>
  <pre><code>ROW_ID | COMPANY_NAME        | REG_ID  ...
  -------+---------------------+--------
  1      | ABC UK LTD          | 992
  2      | ABC UK LTD          | 221
  3      | BOBS BAKERS PLC     | 109
  4      | BOBS BAKERS PLC     | 234
  5      | BOBS BAKERS PLC     | 677
  6      | ADAMS ESTATE AGENTS | 309
  </code></pre>
  <p><strong>Table B</strong></p>
  <pre><code>ROW_ID | COMPANY_NAME        | CH_REF  ...
  -------+---------------------+--------
  4656   | ABC UK LTD          | AE/002
  7986   | ABC UK LTD          | X0294
  8748   | BOBS BAKERS PLC     | F9998
  9820   | BOBS BAKERS PLC     | P2944
  10233  | BOBS BAKERS PLC     | L00091
  14346  | ADAMS ESTATE AGENTS | XM/122
  </code></pre>
  <p>I would like to end up with a query result that matches one-to-one on the <strong>COMPANY_NAME</strong> but doesn't duplicate the join on rows already matched. It doesn't matter about the sequence of the join, as long as all joins are unique. For example:</p>
  <p><strong>Ideal query result</strong></p>
  <pre><code>A.ROW_ID | B.ROW_ID | A.COMPANY_NAME      | B.COMPANY_NAME      | REG_ID | CH_REF
  ---------+----------+---------------------+---------------------+--------+--------
  1        | 4656     | ABC UK LTD          | ABC UK LTD          | 992    | AE/002
  2        | 7986     | ABC UK LTD          | ABC UK LTD          | 221    | X0294
  3        | 8748     | BOBS BAKERS PLC     | BOBS BAKERS PLC     | 109    | F9998
  4        | 9820     | BOBS BAKERS PLC     | BOBS BAKERS PLC     | 234    | P2944
  5        | 10233    | BOBS BAKERS PLC     | BOBS BAKERS PLC     | 677    | L00091
  6        | 14346    | ADAMS ESTATE AGENTS | ADAMS ESTATE AGENTS | 309    | XM/122
  </code></pre>
  <p>Can this be achieved in one SQL query? I would like to try to avoid having to keep iterating on the same dataset to achieve all the matches if possible.</p>