title: How to select from one table and a sub table
body: <p>I have something like the following two tables in SQL Server:</p>
  
  <pre><code>Table1
  MainID Descrip
       1 tree
       2 dog
       3 blah
       4 etc
  
  Table2
  SubID MainID SubDescrip
      1      2 rover
      2      2 fido
  </code></pre>
  
  <p>I want to be able to select all from Table1 and Table2 when it has a matching MainID.</p>
  
  <p>My desired output is as follows:</p>
  
  <pre><code>MainID Descrip SubID SubDescrip
       1 tree     NULL       NULL
       2 dog      NULL       NULL
       2 dog         1      rover
       2 dog         2       fido
       3 blah     NULL       NULL
       4 etc      NULL       NULL
  </code></pre>
  
  <p>I can get all but the second row of the desired output with an outer join, but how do I get that second row as well?</p>
  
  <p>In other words, this:</p>
  
  <pre><code>SELECT m.MainID, m.Descrip, s.SubID, s.SubDescrip
  FROM Table1 m LEFT OUTER JOIN
       Table2 s ON m.MainID = s.MainID
  ORDER BY m.MainID, s.SubID
  </code></pre>
  
  <p>gives me this:</p>
  
  <pre><code>MainID Descrip SubID SubDescrip
       1 tree     NULL       NULL
       2 dog         1      rover
       2 dog         2       fido
       3 blah     NULL       NULL
       4 etc      NULL       NULL
  </code></pre>
  
  <p>but I want this as well:</p>
  
  <pre><code> 2 dog      NULL       NULL
  </code></pre>