title: SQL Join Doubling Some Output
body: <p>I have the following query:</p>
  
  <pre><code>SELECT r.reservation_number, r.rate_type, r.room_type_code, r.room_code, t.client_code, SUM(room_revenue + package_revenue + other_charge_revenue + spa_revenue) AS revenue_total
  FROM nw_src.dbo.fdresrev r
  JOIN nw_src.dbo.fdres_ta_c t ON r.reservation_number = t.reservation_number
  GROUP BY r.reservation_number, r.rate_type, r.room_type_code, r.room_code, t.client_code
  ORDER BY r.reservation_number
  </code></pre>
  
  <p>That returns the results (Minus a field I need) exactly how I want them to look:</p>
  
  <pre><code>reservation_number | rate_type | room_type_code | room_code | client_code | revenue_total
        10002           allincl        1cath          1cath         90            1520
        10003           allincl        1dollh         1dollh        90            2001
        10008           allincl        1cath          1cath         90            1355
  </code></pre>
  
  <p>The issue is I need an additional field from a different table. This other table can have multiple records for any given <code>reservation_number</code>. So for example, it has two records for <code>10002</code> and <code>10008</code>, but only one record for <code>10003</code>.</p>
  
  <p>I have tried the following query to get what I want from this table:</p>
  
  <pre><code>SELECT r.reservation_number, b.created_date_time, r.rate_type, r.room_type_code, r.room_code, t.client_code, SUM(room_revenue + package_revenue + other_charge_revenue + spa_revenue) AS revenue_total
  FROM nw_src.dbo.fdresrev r
  JOIN nw_src.dbo.fdres_ta_c t ON r.reservation_number = t.reservation_number
  JOIN nw_src.dbo.fdroomresc b ON r.reservation_number = b.reservation_number
  GROUP BY r.reservation_number, b.created_date_time, r.rate_type, r.room_type_code, r.room_code, t.client_code
  ORDER BY r.reservation_number
  </code></pre>
  
  <p>This however seems to double the <code>revenue_total</code> of any record that has multiple records in <code>fdroomresc</code> (In this example, <code>10002</code> and <code>10008</code>). The above query is returning:</p>
  
  <pre><code>reservation_number | created_date_time | rate_type | room_type_code | room_code | client_code | revenue_total
         10002      2017-02-03 08:32:00.000 allincl        1cath          1cath          90           3040
         10003      2017-02-03 08:35:00.000 allincl        1dollh         1dollh         90           2001
         10008      2017-02-03 08:43:00.000 allincl        1cath          1cath          90           2710
  </code></pre>
  
  <p>The desired output is:</p>
  
  <pre><code>reservation_number | created_date_time | rate_type | room_type_code | room_code | client_code | revenue_total
         10002      2017-02-03 08:32:00.000 allincl        1cath          1cath          90           1520
         10003      2017-02-03 08:35:00.000 allincl        1dollh         1dollh         90           2001
         10008      2017-02-03 08:43:00.000 allincl        1cath          1cath          90           1355
  </code></pre>