title: Joining multiple table values into one table in order to see things clearly
body: <p>I have 4 tables:</p>
  
  <p>Orders:</p>
  
  <pre><code>OrderID Detail  Discount
  ------------------------
  1001    xxx     True
  1002    xxx     True
  1003    xxx     True
  1004    xxx     False
  1005    xxx     True
  1006    xxx     True
  </code></pre>
  
  <p>OrderDiscounts:</p>
  
  <pre><code>OrderID DiscountTypeID  DiscountID
  ----------------------------------
  1001     1              8
  1002     2              12
  1003     1              9
  1005     2              13
  1006     2              9
  </code></pre>
  
  <p><strong>Coupons</strong> (<code>DiscountTypeID = 1</code>):</p>
  
  <pre><code>CouponID    Title
  ------------------------
  8           CouponTitle8
  9           CouponTitle9
  </code></pre>
  
  <p><strong>Campaigns</strong> (<code>DiscountTypeID = 2</code>):</p>
  
  <pre><code>CampaignID  Title
  --------------------------
  9           CampaignTitle9
  12          CampaignTitle12
  13          CampaignTitle13
  </code></pre>
  
  <p>I need a query that will merge all 4 tables into 1 table which will give some result like:</p>
  
  <p>Result:</p>
  
  <pre><code>OrderID Discount    DiscountType    DiscountTitle
  -----------------------------------------------------
  1001    True        Coupon          CouponTitle8
  1002    True        Campaign        CampaignTitle12
  1003    True        Coupon          CouponTitle9
  1004    False               
  1005    True        Campaign        CampaignTitle13
  1006    True        Campaign        CampaignTitle9
  </code></pre>
  
  <p>Note that some Coupon ID may exist as a Campaign ID. Like '9' exists for both as CouponID and CampaignID in this scenario..</p>
  
  <p>In addition to the query required, a proper explanation about how/why using such commands while building the query would be great as I don't just look for an answer but I'd like to handle similar scenarios by myself as well. Thanks!</p>