title: SQL Server: Multiple table joins with a WHERE clause
body: <p>I'm using SQL Server and I'm having a difficult time trying to get the results from a <code>SELECT</code> query that I want. I've tried joining in different orders and using subqueries but nothing quite works the way I want. Take this contrived example of software applications, with different version levels, that might be installed on peoples computers. </p>
  
  <p>I need to perform a <code>JOIN</code> with a <code>WHERE</code>, but for some reason I can't get the results I want. </p>
  
  <p>Maybe I'm looking at my data wrong, I'm not quite sure why I can't get this to work.</p>
  
  <p><strong>Application</strong> table   </p>
  
  <pre><code>ID  Name
  1   Word
  2   Excel
  3   Powerpoint
  </code></pre>
  
  <p><strong>Software</strong> Table (contains version information for different applications)        </p>
  
  <pre><code>ID  ApplicationID   Version
  1   1             2003
  2   1             2007
  3   2             2003
  4   2             2007
  5   3             2003
  6   3             2007
  </code></pre>
  
  <p><strong>Software_Computer</strong> junction table        </p>
  
  <pre><code>ID  SoftwareID  ComputerID
  1   1           1
  2   4           1
  3   2           2
  4   5           2
  </code></pre>
  
  <p><strong>Computer</strong> table  </p>
  
  <pre><code>ID  ComputerName
  1   Name1
  2   Name2
  </code></pre>
  
  <p>I want a query that I could run where I select a specific computer to display what software version and application is has, but I also want it to display what application it does not have(the version would be a <code>NULL</code> since it doesn't have that software on it)</p>
  
  <pre><code>SELECT Computer.ComputerName, Application.Name, Software.Version
  FROM Computer
  JOIN Software_Computer
      ON Computer.ID = Software_Computer.ComputerID
  JOIN Software
      ON Software_Computer.SoftwareID = Software.ID
  RIGHT JOIN Application
      ON Application.ID = Software.ApplicationID
  WHERE Computer.ID = 1 
  </code></pre>
  
  <p>I want the following result set</p>
  
  <pre><code>ComputerName   Name          Version
  Name1          Word          2003
  Name1          Excel         2007
  Name1          Powerpoint    NULL
  </code></pre>
  
  <p>But I just get </p>
  
  <pre><code>Results
  ComputerName   Name          Version
  Name1          Word           2003
  Name1          Excel          2007
  </code></pre>
  
  <p>I thought the <code>RIGHT JOIN</code> would include all the results in the application table, even if they aren't associated with the computer. What am I missing/doing wrong?</p>