title: Full Outer Join based off multiple fields
body: <p>Here is the situation that I'm facing:</p>
  
  <p>I have two tables A and B.  If records are in table A and not in table B they need to be added to table B.  If records are in table B and not in table A, then they need to be removed out of table B.  The trick here is that it is the mixture of two keys that makes the unique combination</p>
  
  <pre><code>Table A    
  Operation_Key    Part_Key  
  1                1
  1                2
  2                1
  2                3
  
  Table B
  Operation_Key    Part_Key  Record_Key
  1                1         1
  2                1         2
  2                3         3
  2                4         4
  </code></pre>
  
  <p>I am trying to get the right type of query so that the results returned look like</p>
  
  <pre><code>Results
  Operation_Key        Part_Key  Record_Key   Action
  1                    2         NULL         Add
  2                    4         4            Delete
  </code></pre>
  
  <p>The query I have so far looks like similar to this:</p>
  
  <pre><code>CREATE TABLE #Action_Table
  (
    Action VARCHAR(6),
    Action_Bit INT,
    Operation_Key INT,
    Record_Key INT,
    Part_Key INT
  )
  INSERT INTO #Action_Table
  SELECT 
    CASE
      WHEN WS.Operation_Key IS NULL THEN 'Delete'
      WHEN WS.Operation_Key IS NOT NULL THEN 'Add'
    END Action,
    CASE
      WHEN WS.Operation_Key IS NULL THEN '0'
      WHEN WS.Operation_Key IS NOT NULL THEN '1'
    END Action_Bit,
    CASE
      WHEN WS.Operation_Key IS NULL THEN WC.Operation_Key
      WHEN WS.Operation_Key IS NOT NULL THEN WS.Operation_Key
    END Operation_Key,
    CASE
      WHEN WS.Operation_Key IS NULL THEN WC.Record_Key
      WHEN WS.Operation_Key IS NOT NULL THEN NULL
    END Workcenter_Component_Key,
    CASE
      WHEN WS.Operation_Key IS NULL THEN WC.Part_Key
      WHEN WS.Operation_Key IS NOT NULL THEN WS.Part_Key
    END Part_Key
  FROM #WS_Part_Table WS
  FULL OUTER JOIN #WC_Part_Table WC
    ON WC.Part_Key = WS.Part_Key
   AND WC.Operation_Key = WS.Operation_Key
  WHERE (WS.Part_Key IS NULL or WC.Part_Key IS NULL) AND (WS.Operation_Key IS NULL or WC.Operation_Key IS NULL)
  </code></pre>
  
  <p>Both the #WS_Part_Table and the #WC_Part_Table are temp tables that I'm constructing using queries, but my dilemma is that I have to <strong>PRE-QUERY</strong> the #WC_Part_Table query on the operation key that I'm interested in, otherwise I get way too many results.</p>
  
  <p>This is the query that I'm using to create the #WC_Part_Table</p>
  
  <pre><code>    CREATE TABLE #WC_Part_Table
      (
        Operation_Key INT,
        Record_Key INT,
        Part_Key INT
      )
      -- Workcenter Component Table
      INSERT INTO #WC_Part_Table
      SELECT
        O.Operation_Key,
        WC.Record_Key,
        WC.Part_Key
      FROM Workcenter_Component WC
      JOIN Operation O
        ON O.Default_Workcenter_Key = WC.Workcenter_Key
  
   /* There is some reason why this next line is needed */
      WHERE O.Operation_Key = 23149
  </code></pre>