SQL Server: Multiple table joins with a WHERE clause

When using LEFT JOIN or RIGHT JOIN, it makes a difference whether you put the filter in the WHERE or into the JOIN.

See this answer to a similar question I wrote some time ago:
What is the difference in these two queries as getting two different result set?

In short:

• if you put it into the WHERE clause (like you did, the results that aren't associated with that computer are completely filtered out
• if you put it into the JOIN instead, the results that aren't associated with that computer appear in the query result, only with NULL values
  --> this is what you want

The third row you expect (the one with Powerpoint) is filtered out by the Computer.ID = 1 condition (try running the query with the Computer.ID = 1 or Computer.ID is null it to see what happens).

However, dropping that condition would not make sense, because after all, we want the list for a given Computer.

The only solution I see is performing a UNION between your original query and a new query that retrieves the list of application that are not found on that Computer.

The query might look like this:

DECLARE @ComputerId intSET @ComputerId = 1-- your original querySELECT Computer.ComputerName, Application.Name, Software.Version    FROM Computer    JOIN dbo.Software_Computer        ON Computer.ID = Software_Computer.ComputerID    JOIN dbo.Software        ON Software_Computer.SoftwareID = Software.ID    RIGHT JOIN dbo.Application        ON Application.ID = Software.ApplicationID    WHERE Computer.ID = @ComputerIdUNION-- query that retrieves the applications not installed on the given computerSELECT Computer.ComputerName, Application.Name, NULL as VersionFROM Computer, ApplicationWHERE Application.ID not in     (        SELECT s.ApplicationId        FROM Software_Computer sc        LEFT JOIN Software s on s.ID = sc.SoftwareId        WHERE sc.ComputerId = @ComputerId    )AND Computer.id = @ComputerId

try this

DECLARE @Application TABLE(Id INT PRIMARY KEY, NAME VARCHAR(20))INSERT @Application ( Id, NAME )VALUES  ( 1,'Word' ), ( 2,'Excel' ), ( 3,'PowerPoint' )DECLARE @software TABLE(Id INT PRIMARY KEY, ApplicationId INT, Version INT)INSERT @software ( Id, ApplicationId, Version )VALUES  ( 1,1, 2003 ), ( 2,1,2007 ), ( 3,2, 2003 ), ( 4,2,2007 ),( 5,3, 2003 ), ( 6,3,2007 )DECLARE @Computer TABLE(Id INT PRIMARY KEY, NAME VARCHAR(20))INSERT @Computer ( Id, NAME )VALUES  ( 1,'Name1' ), ( 2,'Name2' )DECLARE @Software_Computer  TABLE(Id INT PRIMARY KEY, SoftwareId int, ComputerId int)INSERT @Software_Computer ( Id, SoftwareId, ComputerId )VALUES  ( 1,1, 1 ), ( 2,4,1 ), ( 3,2, 2 ), ( 4,5,2 )SELECT Computer.Name ComputerName, Application.Name ApplicationName, MAX(Software2.Version) VersionFROM @Application Application JOIN @Software Software    ON Application.ID = Software.ApplicationIDCROSS JOIN @Computer ComputerLEFT JOIN @Software_Computer Software_Computer    ON Software_Computer.ComputerId = Computer.Id AND Software_Computer.SoftwareId = Software.IdLEFT JOIN @Software Software2    ON Software2.ID = Software_Computer.SoftwareIDWHERE Computer.ID = 1 GROUP BY Computer.Name, Application.Name