title: How to retrieve a value or null without duplicates when joining three tables?
body: <p>From the following three tables, I need to:</p>
  
  <ol>
  <li>Return one row for each record in People, don't duplicate the person if they have more than one associated contact.  </li>
  <li>Include associated ContactValue, where ContactType is HOMEPHONE, and the start of the date range is prior to now, and the end of the date range is later than now or null.  If a person does not have a home phone, still show the person, but show NULL as the value for ContactValue.</li>
  </ol>
  
  <h2>People</h2>
  
  <p>PersonId (PK, int)<br>
  FullName (nvarchar)</p>
  
  <h2>PeopleContacts</h2>
  
  <p>PersonId (PK, int)<br>
  ContactId (PK, int)<br>
  StartValidDate (PK, datetime)<br>
  EndValidDate (datetime, null)</p>
  
  <h2>Contacts</h2>
  
  <p>ContactId (PK, int)<br>
  ContactType (nvarchar)<br>
  ContactValue (nvarchar)</p>
  
  <p>The <em>People</em> table contains a unique list of people.  <em>PeopleContacts</em> may contain several contact associations for each person, for different contact types, and a date range that the contact is good for.  <em>Contacts</em> contains a list of contact values of certain types.  For example, ContactType of "WORKPHONE" and ContactValue of "(555) 555 5555".</p>
  
  <p>Requirements:</p>
  
  <ol>
  <li>Return one row for each record in People, don't duplicate the person if they have more than one associated contact.  </li>
  <li>Include associated ContactValue, where ContactType is HOMEPHONE, and the start of the date range is prior to now, and the end of the date range is later than now or null.  If a person does not have a home phone, still show the person, but show NULL as the value for ContactValue.</li>
  </ol>
  
  <p>If People contained three rows, with:</p>
  
  <pre><code>1     Jones, Bob
  2     Smith, Bob
  3     Smith, Fred
  </code></pre>
  
  <p>And PeopleContacts contained four rows, with:</p>
  
  <pre><code>1     4    01/01/2012     NULL
  2     1    01/01/2012     02/01/2012
  2     2    02/02/2012     NULL
  3     3    01/01/2012     NULL
  3     4    01/01/2012     NULL
  </code></pre>
  
  <p>And Contacts contained four rows, with:</p>
  
  <pre><code>1     HOMEPHONE     (555) 555 5252
  2     HOMEPHONE     (666) 666 6666
  3     HOMEPHONE     (777) 777 7777
  4     WORKPHONE     (555) 555 5555
  </code></pre>
  
  <p>Output from the correct query, if run after 02/02/2012, should look like:</p>
  
  <pre><code>FullName     ContactValue
  --------     ------------
  Jones, Bob   NULL
  Smith, Bob   (666) 666 6666
  Smith, Fred  (777) 777 7777
  </code></pre>
  
  <p>Jones, Bob has an association to a contact, but it's to a WORKPHONE, so ContactValue should be NULL.  Smith, Bob has associations to two HOMEPHONE records, but only one, (666) 666 6666, still has a valid date range.  Smith, Fred has an association to both a HOMEPHONE and a WORKPHONE.</p>