title: Simplifying LEFT JOINs
body: <p>So I have this query:</p>
  
  <pre><code>  SELECT c1.field1, c2.field2
    FROM table1 c1
    LEFT JOIN table1 c2 ON c1.field1 = c2.field1 AND c1.field2 = :arg1 AND c2.field2 &lt;&gt; :arg1
    LEFT JOIN table2 cc ON c2.field2 = cc.field2 AND cc.field3 = :arg2
    WHERE (c2.field2 IS NULL OR cc.field2 IS NOT NULL)
    ORDER BY ISNULL(c2.field2) ASC
  </code></pre>
  
  <p>This does two things:</p>
  
  <ol>
  <li>Picks up <code>table1.field1</code> for a given <code>field2</code> value.</li>
  <li>Picks up a <code>table1.field2</code> for which field1 is the same as in the previous point that also has a specific propery from a second table. (It's only this complicated because of normalization) If this doesn't exist then return <code>NULL</code>.</li>
  </ol>
  
  <p>While this works it's really ugly. Is there a simpler version doing the same? I am mostly bothered how the second <code>JOIN</code> needs to be a <code>LEFT</code> followed by this <code>WHERE</code>. I felt <code>INNER</code> should be simpler but it doesn't work.</p>