I'm using SQL Server and I'm having a difficult time trying to get the results from a SELECT 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.

I need to perform a JOIN with a WHERE, but for some reason I can't get the results I want.

Maybe I'm looking at my data wrong, I'm not quite sure why I can't get this to work.

Application table

ID  Name
1   Word
2   Excel
3   Powerpoint

Software Table (contains version information for different applications)

ID  ApplicationID   Version
1   1             2003
2   1             2007
3   2             2003
4   2             2007
5   3             2003
6   3             2007

Software_Computer junction table

ID  SoftwareID  ComputerID
1   1           1
2   4           1
3   2           2
4   5           2

Computer table

ID  ComputerName
1   Name1
2   Name2

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 NULL since it doesn't have that software on it)

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 

I want the following result set

ComputerName   Name          Version
Name
Edit
Report