title: SQL - LEFT JOIN multiple conditions - priority
body: <p>I have 2 tables with a structure similar with this:</p>
  
  <blockquote>
    <p>table: user</p>
    
    <p>fields: id, active_office_address_id (this can be 0)</p>
    
    <p>table: user_address </p>
    
    <p>fields: id, user_id, type (home, office)</p>
  </blockquote>
  
  <p>A user can have a "home" address (not mandatory) and multiple "office" addresses. I have a join to get a user address, but I want that if the user have a "home" address to get that address, not "office" address.</p>
  
  <p>So, how can I get "home" address if exists, and only if that not exists to get "office" address. (In reality the query is much more complicated and the join is done on 4-5 tables)</p>
  
  <pre><code>SELECT * FROM user LEFT JOIN user_address ON (user.id = address.user_id AND 
  (user_address.type = "home" OR user.active_office_address_id = user_address.id))
  group by user.id
  </code></pre>