title: How to select value if present or another if not in SQL Server view
body: <p>I need to create a view on a SQL Server database where one of the columns may be drawn from either of two sources, depending what is present. Specifically, we have a series of tables like this (simplified):</p>
  
  <p><strong>Table1</strong>:</p>
  
  <pre><code>personid
  typeid     (one of the values from Table2)
  surname
  forename
    etc...
  </code></pre>
  
  <p><strong>Table2</strong>:</p>
  
  <pre><code>typeid
  typename
    etc...
  </code></pre>
  
  <p><strong>Table3</strong>:</p>
  
  <pre><code>attributeid
  attributename
    etc...
  </code></pre>
  
  <p><strong>Table4</strong>:</p>
  
  <pre><code>valueid
  attributeid (one of the values from table3)
  typeid      (one of the values from table2)
  personid    (one of the values from table1)
  attributevalue
    etc...
  </code></pre>
  
  <p>Now, I need to select (inter alia) the value of "attibutevalue" from table4 that applies to a person. This may seem straightforward (join on personid and you're done) but it's not that simple. There may be no row in Table4 with the personid, in which case we need to default to the attributevalue associated with the type. In other words, in logical terms, it's</p>
  
  <pre><code>if (there exists an attributevalue in t4 associated with personid) 
    use it
  else 
    use attributevalue in t4 associated with typeid
  </code></pre>
  
  <p>I hope this is clear. I've been trying to work out if it could be done with some subtle joins, or coalesce, but haven't been able to figure it out.</p>
  
  <p>Unfortunately I'm not much practised with SQL so some pointers would be much appreciated!</p>
  
  <p>Ultimately I want t be able to write the query using the attributename in t3 (hence including it here) but for now the sticking point is just getting the right value for each attribute.</p>
  
  <p>Thanks in advance</p>
  
  <p><strong>Edit: update in response to the suggestions below</strong> (too long for a comment!):</p>
  
  <p>Thanks for your input. I've tried this but it's not returning what we expect. Here is a sample of the data:</p>
  
  <p>Table1:</p>
  
  <pre><code> PersonID     184
   TypeID        49
   ...etc...
  </code></pre>
  
  <p>Table4:</p>
  
  <pre><code> valueid             423    424   425    426    431    432
   attributeid           4      5     6      7      6      5
   typeid               49     49    49     49     49     49
   personid           NULL   NULL  NULL   NULL    184    184
   attributevalue      yes  track    no     no    yes    pay
     etc...
  </code></pre>
  
  <p>So what we'd expect as the attribute values for person id 184 are </p>
  
  <pre><code>4: yes
  5: pay
  6: yes
  7: no
  </code></pre>
  
  <p>(defaulting to the value associated with the relevant typeid where there's no value associated with a non-null personid)</p>
  
  <p>Modifying the query very slightly for the sake of clarity to:</p>
  
  <pre><code>SELECT distinct t.id, p.AttributeID, isnull(p.attributevalue, p1.attributevalue) AS   attributevalue
      FROM table1 as T
      LEFT JOIN table4 AS p ON p.personID = t.personID
      left JOIN table4  p1 ON p1.typeID = t.typeID 
      where t.ID=184
  </code></pre>
  
  <p>We get back</p>
  
  <pre><code>id     AttributeID  attributevalue
  184        5           pay
  184        6           yes
  </code></pre>
  
  <p>So it has correctly pulled back the values where there is a personid-specific override, but not the defaults.</p>
  
  <p>Any more thoughts would be much appreciated!</p>
  
  <p>Thanks again</p>
  
  <p><strong>2nd Update:</strong></p>
  
  <p>Thanks for the suggestions here. In the end I've gone with one from elsewhere, which was to use a function and call that in each place I need to insert a value. It just returns the appropriate value for a given person/type/attribute combination. Works like a charm.</p>