title: Join on to table with multiple matches but only bring back specified match
body: <p>I have 3 tables.<br>
  Table A contains the data that my query is going to be based on. It contains id1.
  Table B is my connecting table, it contains 2 columns that I'm concerned with: id1 &amp; id2.  </p>
  
  <p>Table C is my lookup table, it contains id2 and ReasonName. I need the column ‘ReasonName’ and all the data from table A.</p>
  
  <p>If I do the following SQL:</p>
  
  <pre><code> SELECT NAME, 
         STARTDATE, 
         ENDDATE, 
         REASON, 
         ID1 
  FROM   TABLEA 
         LEFT JOIN TABLEB 
                ON TABLEA.ID1 = TABLEB.ID1
  </code></pre>
  
  <p>I am then able to do a second join on to TableC, however this is where the problem lies.
  There are multiple matches in TableC so therefore my totalrows increases. I need to avoid this.
  See image for what TableC looks like:</p>
  
  <p><img src="https://i.stack.imgur.com/Ymazn.png" alt="enter image description here"></p>
  
  <p>If my SQL statement now looks like this:</p>
  
  <pre><code> SELECT TABLEA.NAME, 
         TABLEA.STARTDATE, 
         TABLEA.ENDDATE, 
         TABLEA.REASON, 
         TABLEA.ID1, 
         TABLEC.REASONNAME 
  FROM   TABLEA 
         LEFT JOIN TABLEB 
                ON TABLEA.ID1 = TABLEB.ID1 
         LEFT JOIN TABLEC 
                ON TABLEB.ID2 = TABLEC.ID2 
  </code></pre>
  
  <p>Then my rows increase by around 1000. This is because it is quite possible that TableA.id1 matches multiple id2’s found in TableC. It then duplicates rows apart from the different TableC.ReasonName.</p>
  
  <p>In TableC there is one ReasonName that I am concerned with. For this example I will say it is ‘reasonf’.</p>
  
  <p>What I need is to bring through ReasonName from TableC, BUT only the ones that contain reasonf, the rest I want to say NULL or to do a COALESCE with ‘No Reason’.
  I have tried putting a WHERE clause into the statement. See SQL:</p>
  
  <pre><code> SELECT TABLEA.NAME, 
         TABLEA.STARTDATE, 
         TABLEA.ENDDATE, 
         TABLEA.REASON, 
         TABLEA.ID1, 
         TABLEC.REASONNAME 
  FROM   TABLEA 
         LEFT JOIN TABLEB 
                ON TABLEA.ID1 = TABLEB.ID1 
         LEFT JOIN TABLEC 
                ON TABLEB.ID2 = TABLEC.ID2 
  WHERE  TABLEC.ID2 = 'asd1f5as98a4' 
  </code></pre>
  
  <p>But then it will only bring though those records where there is a match with ‘reasonf’, I want it to display reasonf people and ignore the rest (leave as null or something) so I have no duplicates but my full result set.</p>
  
  <p>I'm thinking I may need to change to a right join or possibly change the WHERE but I'm not entirely sure.</p>