title: Selecting from joined tables when relative rows might not exist
body: <p>TSQL gurus, I'm faced with two tables that look something like this:</p>
  
  <p>Table - Names</p>
  
  <pre><code>ID(pk)  Name
  1       Peter
  2       Paul
  3       Mary
  4       John
  5       Fred
  </code></pre>
  
  <p>Table - Cars</p>
  
  <pre><code>NameID(pk)  FieldNum    CarText
  1           1           Ford
  1           2           abcd123
  1           3           Mustang
  1           4           1967
  1           6           Yellow
  2           2           qwer456
  2           4           1980
  3           1           Chevrolet
  3           3           Camaro
  4           1           Chrysler
  4           2           zxcv789
  5           4           1970
  </code></pre>
  
  <p>Since all of the names in the first (names) table are required but only FieldNum = 2 (if it exists) is of interest in the second table, the desired output would look something like this:</p>
  
  <pre><code>Name        CarText
  ---------------------
  Peter       abcd123
  Paul        qwer456
  Mary        
  John        zxcv789
  Fred
  </code></pre>
  
  <p>Or for that matter, an output with some form of null placeholder along these lines would also be usable:</p>
  
  <pre><code>Name        CarText
  ---------------------
  Peter       abcd123
  Paul        qwer456
  Mary        foo
  John        zxcv789
  Fred        foo
  </code></pre>
  
  <p>Various docs suggest that changing the table joins to a LEFT (or LEFT OUTER) join is the solution. No problem.</p>
  
  <pre><code>SELECT Name, CarText
   FROM Names
   LEFT JOIN Cars on Names.ID = Cars.NameID
  WHERE Cars.FieldNum = 2
  </code></pre>
  
  <p>But this approach is delivering:</p>
  
  <pre><code>Name        CarText
  ---------------------
  Peter       abcd123
  Paul        qwer456
  John        zxcv789
  </code></pre>
  
  <p>So after amateurishly experimenting with RANK(), ROWNUM, CASE and WHERE EXISTS/NOT EXISTS statements, it feels like the answer is embarassingly close but after a couple of days I just can't nail it.</p>
  
  <p>How can I achieve the desired output, with one table being mandatory but a joined table only contributing a relevant field if/when one exists (or if necessary, in lieu of a related field, some sort of nullish value)?</p>