sql server - Full outer join on one criteria, inner join on another -

Let me include the header and extension rows in a result set Required:

(sample DDL and insert ):

Order :

  OrderID Order Date Buy Currency IDsMount BuyRacket ===================================== ====== ========== 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   
  ORDERDETAILS :  
  OrderID CurrencyID SellAmount SellRate ======= ========== ===== ===== ==================================================================== I Hen  OrderID  and  CurrencyID  would like to add to / would like:  
  OrderID CurrencyID BuyAmount BuyRate SellAmount SellRate ======= = =============================================================== , 243893 10000 1 1 8 Zero zero 12,384 .9638 1 9 69.48 1 Zero zero   
 sample script  
  - Use scratch - a temporary `command 'And' Order Ls` tables: IF OBJECT_ID ('tempdb. # Order') & gt; 0 drop table #orders table #orders Create (OrderID zero not int, OrderDate datetime NULL, CurrencyID not int, BuyAmount money minus, BuyRate real not NULL) if object_id ( 'tempdb .. # ORDERDETAILS') & gt; 0 drop table #OrderDetails create table #OrderDetails (OrderID not int, CurrencyID insert not int ,, SellAmount money NULL not SellRate real zero) - ** Sample data minus: ** insert #orders (OrderID, OrderDate CurrencyID, BuyAmount, BuyRate) values ​​(1, '20, 110,901 15:57:00 ', 7, 12173.60, 1.2438 9 33) #orders (OrderID, OrderDate CurrencyID, BuyAmount, BuyRate) values ​​(insert 1, 20, 110 901 15:57:00 ', 9, 69.48, 1) #OrderDetails investigation into the #OrderDetails (OrderID, CurrencyID, SellAmount, SellRate) vALUES (1, 7, 10000, 1) insert (OrderID, CurrencyID, SellAmount, SellRate) vALUES (1, 8, 12,384, .9638) / * desired output: OrderID CurrencyID BuyAmount BuyRate SellAmount S EllRate ======= ========================================== === 1 7 12173,60 1,243893 10000 1 1 8 zero zero 12,384 .9638 9 69.48 1 zero zero * /   
 I can do this  Right Outdoor Join < Find a combination of / code>,  full external join ,  COALESCE , which can produce my desired output.  
 
  Update:   
 It is also possible that  OrderDetails does not match   CurrencyID  By order   Table:  
  Orders :  
  OrderID CurrencyID BuyAmount BuyRate ==== === ================================================== ===================================  ORDERDETAILS :  
  OrderID CurrencyID SellAmount SellRate =============================================    
 
 
 So, you have tried what it is?  
  Select as COALESCE (o.OrderID, od.OrderID) OrderID, COALESCE (o.CurrencyID, od.CurrencyID) CurrencyID, o.BuyAmount, o.BuyRate, od.SellAmount As in, Ode. O full outer # oder oder oder oder order oder order oder order order from cell rate OderID and o Corralized = ODi CARRIASID