title: Joining Three tables at the same time?
body: <p>I have a table with multiple transaction types. Currently I'm only pulling the adjustments and payments, but I am needing to match the IDs of the types to their descriptions. For example, my transactions table has <code>paytype_id</code> and <code>adjustment_id</code> which range 1-100 for each. I have two other tables <code>dbo.paytype</code> and <code>dbo.adjustments</code> that have the distinct <code>paytype_id</code> and <code>adjustment_id</code> along with the <code>pay_desc</code> and <code>adj_desc</code> fields that describe what kind they are. Issue I'm running into is that any given transaction will only have a <code>paytype_id</code> or an <code>adjustment_id</code> but not both. So if I attempt to join one table, then the other I lose the NULL values of the secondary IDs</p>
  
  <p>This query will pull the <code>paytype_id</code> descriptions but will remove any transactions with <code>adjustment_id</code> due to the <code>payment_id</code> being NULL for them.</p>
  
  <pre><code>SELECT 
      t.tran_num, t.resp_party_id, t.Total, 
      t.paytype_id, t.adjustment_id, t.clinic, 
      t.date_entered, p.pay_desc
  FROM 
      adjpay_vw t 
  CROSS JOIN 
      paytype p
  WHERE 
      (t.paytype_id = p.paytype_id AND t.clinic = p.clinic)
  </code></pre>
  
  <p>So I'm wondering how I can pull both the <code>adj_desc</code> from <code>dbo.adjustments</code> and the <code>pay_desc</code> from <code>dbo.paytype</code></p>