title: MYSQL query (with joins and virtual values)
body: <p>I have 3 InnoDB tables: <strong>emails</strong>, <strong>websites</strong> and <strong>subscriptions</strong>.</p>
  
  <p><strong>emails</strong> table has <code>id</code> and <code>email</code> columns.</p>
  
  <p><strong>websites</strong> table has <code>id</code> and <code>address</code> columns.</p>
  
  <p><strong>subscriptions</strong> table has <code>id</code>, <code>email_id</code> and <code>website_id</code> columns.</p>
  
  <p>What I'm tring to do is supply an email and return a table with columns <code>address</code> and <code>subscribed</code>. The former is a list of all the addresses in the <strong>websites</strong> table and the latter gets value 1 if the supplied email address has an occurence in the <strong>subscriptions</strong> table with <code>website_id</code> set to that website, or 0 otherwise. But I'm willing to retain all the websites even if the user is not found.</p>
  
  <p>The point I'm stuck is where I should change the value of the virtual column <code>subscribed</code> from 0 to 1 when that email has that record.</p>
  
  <p>Here's my query so far. Does anybody know how to do this?</p>
  
  <pre><code>SELECT `address`, "0" AS `subscribed`
  /* 0 becomes 1 for the websites email has subscribed to */
  FROM `websites` a
  LEFT JOIN (
      SELECT s.`website_id` FROM `subscriptions` s
      RIGHT JOIN (
          SELECT `id` AS `email_id` FROM `emails`
          WHERE `email`='someone@mail.com' LIMIT 1) e
      ON s.`email_id`=e.`email_id`) l
  ON l.`website_id`=a.`id`
  </code></pre>
  
  <p>And here are the example outputs for the desired values for the <code>subscribed</code> column:</p>
  
  <ul>
  <li>If email is not found in the <code>emails</code> table all the rows get value 0</li>
  <li>If email is found in the <code>emails</code> table...
  <ul>
  <li>if it is not found in <code>subscriptions</code> table all the rows get value 0</li>
  <li>if it is found in <code>subscriptions</code> table, the appropriate address rows get value 1</li>
  </ul></li>
  </ul>
  
  <p>Let me know if I couldn't wxplain it well. Does anytbody know what I should alter in my query?</p>
  
  <p>Thanks in advance.</p>