title: Mysql, as 1 query, if row does not exist, do other query
body: <p>For a preferences module I have "system defaults", and "user preferences".<br>
  If there is no personal/user preference stored, then use the system default values instead.</p>
  
  <p>Here is my system preferences table: </p>
  
  <pre><code>mysql&gt; desc rbl;
  +-------------+---------------------+------+-----+---------+-------+
  | Field       | Type                | Null | Key | Default | Extra |
  +-------------+---------------------+------+-----+---------+-------+
  | id          | varchar(3)          | NO   | PRI |         |       | 
  | rbl_url     | varchar(100)        | NO   |     |         |       | 
  | description | varchar(100)        | NO   |     |         |       | 
  | is_default  | tinyint(1) unsigned | YES  |     | 1       |       | 
  +-------------+---------------------+------+-----+---------+-------+
  4 rows in set (0.00 sec)
  </code></pre>
  
  <p>Example data from system prefs:</p>
  
  <pre><code>mysql&gt; select * from rbl;
  +----+----------------------+------------------------------+------------+
  | id | rbl_url              | description                  | is_default |
  +----+----------------------+------------------------------+------------+
  | 1  | sbl-xbl.spamhaus.org | Spamhaus SBL-XBL             |          1 | 
  | 2  | pbl.spamhaus.org     | Spamhaus PBL                 |          1 | 
  | 3  | bl.spamcop.net       | Spamcop Blacklist            |          1 | 
  | 4  | rbl.example.com      | Example RBL - not functional |          0 | 
  +----+----------------------+------------------------------+------------+
  </code></pre>
  
  <p>... and Query for system defaults:</p>
  
  <pre><code>mysql&gt; SELECT rbl_url FROM rbl WHERE is_default='1';
  +----------------------+
  | rbl_url              |
  +----------------------+
  | sbl-xbl.spamhaus.org | 
  | pbl.spamhaus.org     | 
  | bl.spamcop.net       | 
  +----------------------+
  3 rows in set (0.01 sec)
  </code></pre>
  
  <p>So far so good. 
  OK. Now I need a user preferences table, and I came up with this:</p>
  
  <pre><code>mysql&gt; desc rbl_pref;
  +-----------+-----------------------+------+-----+---------+----------------+
  | Field     | Type                  | Null | Key | Default | Extra          |
  +-----------+-----------------------+------+-----+---------+----------------+
  | id        | mediumint(8) unsigned | NO   | PRI | NULL    | auto_increment | 
  | domain_id | mediumint(8) unsigned | NO   |     | NULL    |                | 
  | rbl_id    | tinyint(1) unsigned   | NO   |     | NULL    |                | 
  +-----------+-----------------------+------+-----+---------+----------------+
  3 rows in set (0.00 sec)
  </code></pre>
  
  <p>(FYI - A "user" is represented by "domain_id". )</p>
  
  <p>Let's view the preferences of a specific user who has personalized preferences saved:</p>
  
  <pre><code>mysql&gt; select * from rbl_pref where domain_id='2277';
  +----+-----------+--------+
  | id | domain_id | rbl_id |
  +----+-----------+--------+
  |  4 |      2277 |      1 | 
  |  5 |      2277 |      2 | 
  |  6 |      2277 |      4 | 
  +----+-----------+--------+
  3 rows in set (0.00 sec)
  </code></pre>
  
  <p>... again, but in a simpler format:</p>
  
  <pre><code>mysql&gt; SELECT rbl.rbl_url FROM rbl_pref,rbl 
         WHERE rbl_pref.rbl_id=rbl.id AND domain_id='2277';
  +----------------------+
  | rbl_url              |
  +----------------------+
  | sbl-xbl.spamhaus.org | 
  | pbl.spamhaus.org     | 
  | rbl.example.com      | 
  +----------------------+
  3 rows in set (0.00 sec)
  </code></pre>
  
  <p>.. so far so good. If a user has stored a preference, a result is found.</p>
  
  <p>The problem example now is,  user 1999 has no custom preferences. 
  In place of the "Empty set" result, I want the system defaults.</p>
  
  <pre><code>mysql&gt; SELECT rbl.rbl_url FROM rbl_pref,rbl 
         WHERE rbl_pref.rbl_id=rbl.id AND domain_id='1999';
  Empty set (0.00 sec)
  </code></pre>
  
  <p>I was excited to find a very similar question:
  <a href="https://stackoverflow.com/questions/4977951/mysql-if-row-doesnt-exist-grab-default-value">mysql if row doesn&#39;t exist, grab default value</a>
  However after a couple of days trial and error and documentation review, I could not translate that answer over to here.</p>
  
  <p>Like the above question, this must be done as a single MySQL query. I am not actually making this query from PHP, but from Exim macros (and it is a very picky language... best to feed it "one liners" as variable assignments, as I try to do here.. )</p>
  
  <p>UPDATE: Tried one type of a UNION query suggested by @Biff McGriff, below. The table did not display in my comment reply, so here it is again:</p>
  
  <pre><code>mysql&gt; SELECT rbl.rbl_url FROM rbl_pref,rbl 
         WHERE rbl_pref.rbl_id=rbl.id AND domain_id='2277' 
         UNION SELECT rbl_url FROM rbl WHERE is_default='1';
  +----------------------+
  | rbl_url              |
  +----------------------+
  | sbl-xbl.spamhaus.org | 
  | pbl.spamhaus.org     | 
  | rbl.example.com      | 
  | bl.spamcop.net       | 
  +----------------------+
  4 rows in set (0.00 sec)
  </code></pre>
  
  <p>As you can see above, user 2277 did not opt in to rbl_id 3 (bl.spamcop.net), but that's showing up anyways.</p>
  
  <p>What my UNION query <em>seems</em> to be doing is combining the result set. So user_pref acts as "in addition to" global defaults, and I was assuming/expecting I would get a result set matching either half of the query.</p>
  
  <p>So my question now is, is it better (or possible, how) to solve this as "either result set" (either subquery on either side of the UNION)? OR do I really need a new field on rbl_pref, called for example "enabled". The latter seems to be more correct - that I need something in rbl_pref to explicitly designate opt-in or opt-out (other than the implicit "that pref is not here - no rbl_id=3 - in the over ridden user result SET")</p>
  
  <p>UPDATE: All set, thanks @Imre L, and everyone else. I learned something through this example.</p>