title: Corrections in Left Outer Join Query to work with multiple criteria
body: <p>I want results from three tables where the results from the first table are must. For this I used Left Outer Join to link tables on the basis of <strong>sno</strong> (Primary Key). The query runs fine but I have an added complexity when I want to pick results from the third table on different criteria.</p>
  
  <p>The question will clear when seeing how I am trying to retrieve results:</p>
  
  <pre><code>SELECT * FROM tw_main MainTable 
      LEFT OUTER JOIN tw_details TW
          ON TW.sno = MainTable.sno
      LEFT OUTER JOIN tw_typedetails CWR
          ON CWR.sno = MainTable.sno WHERE CWR.twcatname = 'CWR'
      LEFT OUTER JOIN tw_typedetails OHT
          ON OHT.sno = MainTable.sno WHERE OHT.twcatname = 'OHT'
      LEFT OUTER JOIN tw_typedetails DS
          ON DS.sno = MainTable.sno WHERE DS.twcatname = 'DS'             
      WHERE
          MainTable.sno = 420;
  </code></pre>
  
  <p>This query works fine if I use the third table (tw_typedetails) only once, but I want separate resultset from the same table but with different WHERE clause.</p>
  
  <p>What corrections are needed? I am using SQL Server 2008 R2 Express. This query is supposed to run from Crystal Reports command.</p>