title: Join table 1 to either column 1 or 2 from table 2 without duplicates
body: <p>[MS SQL 2008]</p>
  
  <p>I have tables (all columns are string names):
  A: two columns relating some datafield to an owning entity
  B: three columns defining a hierarchy of entities</p>
  
  <p>I need to create a singe table of the whole hierarchy (including all rows not existing in both tables), but the key column in table A (shown as Acol2) can be in either column 1 or 2 of table B...</p>
  
  <pre><code>A:                         B:
   Acol1 | Acol2                Bcol1 | Bcol2 | Bcol3
  -------+------              --------+-------+------
     A   |   B                    B   |   X   |   Y
     C   |   D                    Q   |   X   |   Y
     E   |   F                    H   |   D   |   Z
     G   |   H                    W   |   V   |   U
  </code></pre>
  
  <p>The output should be</p>
  
  <pre><code>Hierarchy:
   Acol1 | Bcol1 | Bcol2 | Bcol3
  -------+-------+-------+------
     A   |   B   |   X   |  Y
    Null |   Q   |   X   |  Y
     C   |  Null |   D   |  Z
     G   |   H   |   D   |  Z
     E   |  Null |  Null | Null
    Null |   W   |   V   |  U 
  
  Logic (also added to original):
      If A has no record in B, show A with all Null
      If A has record in Bcol1, show A with full row B
      If A has record in Bcol2, show A with Null, Bcol2, Bcol3
      If B has no record in A, show B with Null for Acol1
  </code></pre>
  
  <p>I have tried all sorts of UNIONs of two separate JOINs, but can't seem to get rid of extraneous rows...</p>
  
  <ol>
  <li><code>B LEFT JOIN A ON Acol2=Bcol1 UNION B LEFT JOIN A ON Acol2=Bcol2;</code> 
  gives duplicate rows, as the second part of the union has to set Bcol1 to NULL
  (perhaps one solution is a way to remove this duplicate NULL row?)</li>
  <li><code>B INNER JOIN A ON Acol2=Bcol1 UNION B INNER JOIN A ON Acol2=Bcol2;</code>
  Obviously removes all the rows from A and B that have no shared keys
  (solution as to easy way to regain just those rows?)</li>
  </ol>
  
  <p>Any idea appreciated!</p>
  
  <p>To play:
  [SQL removed - see fiddle in reply comments]</p>