title: MySQL perform subquery if count is zero
body: <p>I have three tables: <code>monthly_revenue</code>, <code>currencies</code> and <code>foreign_exchange</code>. </p>
  
  <h2>monthly_revenue table</h2>
  
  <pre><code>|------------------------------------------------------|
  | id | product_id | currency_id | value | month | year |
  |------------------------------------------------------|
  | 1  | 1          | 1           | 100   | 1     | 2015 |
  | 2  | 1          | 2           | 125   | 1     | 2015 |
  | 3  | 1          | 3           | 115   | 1     | 2015 |
  | 4  | 1          | 1           | 100   | 2     | 2015 |
  | 5  | 1          | 2           | 125   | 2     | 2015 |
  | 6  | 1          | 3           | 115   | 2     | 2015 |
  |------------------------------------------------------|
  </code></pre>
  
  <h2>foreign_exchange table</h2>
  
  <pre><code>|---------------------------------------|
  | id | base | target | rate | rate_date |
  |---------------------------------------|
  | 1  | GBP  | USD    | 1.6  |2015-01-01 |
  | 2  | GBP  | USD    | 1.62 |2015-01-15 |
  | 3  | GBP  | USD    | 1.61 |2015-01-31 |
  | 4  | EUR  | USD    | 1.2  |2015-01-01 |
  | 5  | EUR  | USD    | 1.4  |2015-01-15 |
  | 6  | EUR  | USD    | 1.4  |2015-01-31 |
  | 7  | GBP  | EUR    | 1.4  |2015-01-01 |
  | 8  | GBP  | EUR    | 1.45 |2015-01-15 |
  | 9  | GBP  | EUR    | 1.44 |2015-01-31 |
  |---------------------------------------|
  </code></pre>
  
  <p>From this, we can see the average fx rates: </p>
  
  <ul>
  <li>GBP > USD in January is 1.61</li>
  <li>EUR > USD in January is 1.33</li>
  <li>GBP > EUR in January is 1.43</li>
  </ul>
  
  <p>No rates are available for USD as a base currency, and no rates are available for February.</p>
  
  <h2>currencies table</h2>
  
  <pre><code>|-----------|
  | id | name |
  |-----------|
  | 1  | GBP  |
  | 2  | USD  |
  | 3  | EUR  |
  |-----------|
  </code></pre>
  
  <h2>What i'm trying to achieve</h2>
  
  <p>Each row within the <code>monthly_revenue</code> table can have a different <code>currency_id</code>, as orders are placed is different currencies. I want to see all revenue for a given month, in a common currency. So, rather than looking at all revenue in January in GBP, and then separately looking at all revenue in January in USD, I'd like to get one value for all revenue in January - converted to USD (for example). </p>
  
  <p>This can be calculated for each row, using the following (using January for this example): </p>
  
  <p>revenue value <strong>x</strong> average fx rate for January between base and target currency</p>
  
  <p>If I have 50 orders in January, in 4 different currencies, this let's me see all revenue in any single currency.</p>
  
  <h2>Example - get all revenue in January, in USD</h2>
  
  <p>This should return:</p>
  
  <pre><code>|------------------------------------------------------|
  | id | product_id | currency_id | value | month | year |
  |------------------------------------------------------|
  | 1  | 1          | 1           | 100   | 1     | 2015 |
  | 2  | 1          | 2           | 125   | 1     | 2015 |
  | 3  | 1          | 3           | 115   | 1     | 2015 |
  |------------------------------------------------------|
  </code></pre>
  
  <p>However, rows 1 and 3 are not in USD (these are GBP, and EUR respectively).</p>
  
  <p>What I'd like to see is each row returned with the average FX rate that is being converted to, and a <code>converted</code> column. For example:</p>
  
  <pre><code>|-------------------------------------------------------------------------|
  | id | prod_id | currency_id | value | month | year | fx_avg | converted  |
  |-------------------------------------------------------------------------|
  | 1  | 1       | 1           | 100   | 1     | 2015 | 1.61   | 161        |
  | 2  | 1       | 2           | 125   | 1     | 2015 | 1      | 125        |
  | 3  | 1       | 3           | 115   | 1     | 2015 | 1.33   | 152.95     |
  |-------------------------------------------------------------------------|
  </code></pre>
  
  <h2>Where I'm at</h2>
  
  <p>I can currently get the basic calculation done using the query below, but a couple of key features are lacking:</p>
  
  <ul>
  <li><p>If there is no FX rate available (for example for future dates where of course an FX rate isn't available) then the entire row is ignored. What I'd like in this instance is for the latest month's average to be used.</p></li>
  <li><p>If the calculation is being performed where the target currency is the same as the base currency, the entire row is ignored (as there is no record in the FX table where the base equals the target). In this instance, the rate should be hard defined as 1.</p></li>
  </ul>
  
  <h2>Query so far</h2>
  
  <pre><code>SELECT 
      r.value * IFNULL(AVG(fx.rate),1) as converted, AVG(fx.rate) as averageFx, 
      r.*, fx.*
  FROM 
      foreign_exchange fx, monthly_revenue r, order_headers h
  WHERE 
      fx.base IN (SELECT name FROM currencies WHERE id = r.currency_id) AND 
      r.order_header_id = h.id AND 
      fx.target = 'USD' AND 
      MONTH(fx.rate_date) = r.month AND
      YEAR(fx.rate_date) = r.year AND
      r.year = 2015
  GROUP BY r.id
  ORDER BY month ASC
  </code></pre>
  
  <p>If there are no records available for FX, it looks like a separate subquery should be performed to get the average of the latest month's rates.</p>
  
  <p>Any input would be appreciated. If any further info is required, please post a comment.</p>
  
  <p>Thanks.</p>
  
  <p><strong>Edit</strong> <a href="http://sqlfiddle.com/#!9/33def/2">Here is a SQFiddle</a> which has the example schemas and the code which highlights the issue.</p>