title: Full outer join on one criteria, inner join on another
body: <p>i need to join <em>Header</em> and <em>Detail</em> rows into one resultset:</p>
  
  <p>(sample DDL and <code>inserts</code> to follow):</p>
  
  <p><strong>Orders</strong>:  </p>
  
  <pre><code>OrderID  OrderDate                CurrencyID  BuyAmount  BuyRate 
  =======  =======================  ==========  =========  ========
  1        2011-09-01 15:57:00.000  7           12173.60   1.243893
  1        2011-09-01 15:57:00.000  9           69.48      1
  </code></pre>
  
  <p><strong>OrderDetails</strong>:</p>
  
  <pre><code>OrderID  CurrencyID  SellAmount  SellRate
  =======  ==========  ==========  ========
  1        7           10000       1
  1        8           12384       0.9638
  </code></pre>
  
  <p>i want them joined on <code>OrderID</code> and <code>CurrencyID</code>:</p>
  
  <pre><code>OrderID  CurrencyID  BuyAmount  BuyRate   SellAmount  SellRate
  =======  ==========  =========  ========  ==========  ========
  1        7           12173.60   1.243893  10000       1
  1        8           NULL       NULL      12384       0.9638
  1        9           69.48      1         NULL        NULL
  </code></pre>
  
  <h2>Sample scripts</h2>
  
  <pre><code>--USE Scratch
  
  --Create a temporary `Orders` and, `OrderDetails` tables:
  IF OBJECT_ID('tempdb..#Orders') &gt; 0 DROP TABLE #Orders
  CREATE TABLE #Orders
  (
      OrderID int NOT NULL,
      OrderDate datetime NOT NULL,
      CurrencyID int NOT NULL,
      BuyAmount money NOT NULL,
      BuyRate real NOT NULL
  )
  
  IF OBJECT_ID('tempdb..#OrderDetails') &gt; 0 DROP TABLE #OrderDetails
  CREATE TABLE #OrderDetails
  (
      OrderID int NOT NULL,
      CurrencyID int NOT NULL,
      SellAmount money NOT NULL,
      SellRate real NOT NULL
  )
  
  -- **Insert sample data:**
  
  INSERT INTO #Orders (OrderID, OrderDate, CurrencyID, BuyAmount, BuyRate) 
  VALUES (1, '20110901 15:57:00', 7, 12173.60, 1.2438933)
  INSERT INTO #Orders (OrderID, OrderDate, CurrencyID, BuyAmount, BuyRate) 
  VALUES (1, '20110901 15:57:00', 9, 69.48, 1)
  
  
  INSERT INTO #OrderDetails (OrderID, CurrencyID, SellAmount, SellRate)
  VALUES (1, 7, 10000, 1)
  INSERT INTO #OrderDetails (OrderID, CurrencyID, SellAmount, SellRate)
  VALUES (1, 8, 12384, 0.9638)
  
  
  /*Desired Output:
  OrderID  CurrencyID  BuyAmount  BuyRate   SellAmount  SellRate
  =======  ==========  =========  ========  ==========  ========
  1        7           12173.60   1.243893  10000       1
  1        8           NULL       NULL      12384       0.9638
  1        9           69.48      1         NULL        NULL
  
  */
  </code></pre>
  
  <p>i can't find a combination of <code>RIGHT OUTER JOIN</code>, <code>FULL OUTER JOIN</code>, <code>COALESCE</code> that can produce my desired output.</p>
  
  <hr>
  
  <p><strong>Update:</strong></p>
  
  <p>It's also possible that <code>OrderDetails</code> doesn't contain a matching <code>CurrencyID</code> from the <code>Orders</code> table:</p>
  
  <p><strong>Orders</strong>:  </p>
  
  <pre><code>OrderID  CurrencyID  BuyAmount  BuyRate 
  =======  ==========  =========  ========
  1        7           12173.60   1.243893
  1        9           69.48      1
  </code></pre>
  
  <p><strong>OrderDetails</strong>:</p>
  
  <pre><code>OrderID  CurrencyID  SellAmount  SellRate
  =======  ==========  ==========  ========
  1        8           12384       0.9638
  </code></pre>