'SQL Server: Multiple table joins with a WHERE clause

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
Name1          Word          2003
Name1          Excel         2007
Name1          Powerpoint    NULL

But I just get

Results
ComputerName   Name          Version
Name1          Word           2003
Name1          Excel          2007

I thought the RIGHT JOIN would include all the results in the application table, even if they aren't associated with the computer. What am I missing/doing wrong?

Solution 1:^[1]

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 int
SET @ComputerId = 1

-- your original query
SELECT 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 = @ComputerId

UNION

-- query that retrieves the applications not installed on the given computer
SELECT Computer.ComputerName, Application.Name, NULL as Version
FROM Computer, Application
WHERE 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

Solution 2:^[2]

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) Version
FROM @Application Application 
JOIN @Software Software
    ON Application.ID = Software.ApplicationID
CROSS JOIN @Computer Computer
LEFT JOIN @Software_Computer Software_Computer
    ON Software_Computer.ComputerId = Computer.Id AND Software_Computer.SoftwareId = Software.Id
LEFT JOIN @Software Software2
    ON Software2.ID = Software_Computer.SoftwareID
WHERE Computer.ID = 1 
GROUP BY Computer.Name, Application.Name

Solution 3:^[3]

You need to do a LEFT JOIN.

SELECT Computer.ComputerName, Application.Name, Software.Version
FROM Computer
JOIN dbo.Software_Computer
    ON Computer.ID = Software_Computer.ComputerID
LEFT JOIN dbo.Software
    ON Software_Computer.SoftwareID = Software.ID
RIGHT JOIN dbo.Application
    ON Application.ID = Software.ApplicationID
WHERE Computer.ID = 1

Here is the explanation:

The result of a left outer join (or simply left join) for table A and B always contains all records of the "left" table (A), even if the join-condition does not find any matching record in the "right" table (B). This means that if the ON clause matches 0 (zero) records in B, the join will still return a row in the result—but with NULL in each column from B. This means that a left outer join returns all the values from the left table, plus matched values from the right table (or NULL in case of no matching join predicate). If the right table returns one row and the left table returns more than one matching row for it, the values in the right table will be repeated for each distinct row on the left table. From Oracle 9i onwards the LEFT OUTER JOIN statement can be used as well as (+).

Solution 4:^[4]

SELECT p.Name, v.Name
FROM Production.Product p
JOIN Purchasing.ProductVendor pv
ON p.ProductID = pv.ProductID
JOIN Purchasing.Vendor v
ON pv.BusinessEntityID = v.BusinessEntityID
WHERE ProductSubcategoryID = 15
ORDER BY v.Name;

Solution 5:^[5]

You almost have it. You need a Right join to the application, So it knows that the right table which is application is important

SELECT Computer.ComputerName, Application.Name, Software.Version
FROM Computer
JOIN Software_Computer
    ON Computer.ID = Software_Computer.ComputerID
Right JOIN Software
    ON Software_Computer.SoftwareID = Software.ID
RIGHT JOIN Application
    ON Application.ID = Software.ApplicationID
WHERE Computer.ID = 1

Solution 6:^[6]

Try this working fine....

SELECT computer.NAME, application.NAME,software.Version FROM computer LEFT JOIN software_computer ON(computer.ID = software_computer.ComputerID)
 LEFT JOIN software ON(software_computer.SoftwareID = Software.ID) LEFT JOIN application ON(application.ID = software.ApplicationID) 
 where computer.id = 1 group by application.NAME UNION SELECT computer.NAME, application.NAME,
 NULL as Version FROM computer, application WHERE application.ID not in ( SELECT s.applicationId FROM software_computer sc LEFT JOIN software s 
 on s.ID = sc.SoftwareId WHERE sc.ComputerId = 1 ) 
 AND computer.id = 1

Solution 7:^[7]

select C.ComputerName, S.Version, A.Name from Computer C inner join Software_Computer SC on C.Id = SC.ComputerId Inner join Software S on SC.SoftwareID = S.Id Inner join Application A on S.ApplicationId = A.Id ;

Solution 8:^[8]

SELECT Computer.Computer_Name, Application1.Name, Max(Soft.[Version]) as Version1
FROM Application1
inner JOIN Software
    ON Application1.ID = Software.Application_Id
cross join Computer
Left JOIN Software_Computer
    ON Software_Computer.Computer_Id = Computer.ID and Software_Computer.Software_Id = Software.Id
Left JOIN Software as Soft
    ON Soft.Id = Software_Computer.Software_Id
WHERE Computer.ID = 1 
GROUP BY Computer.Computer_Name, Application1.Name

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1
Solution 2	Oleg Dok
Solution 3	aF.
Solution 4	Behnam Mohammadi
Solution 5	Jens
Solution 6	Abhinav Singh Maurya
Solution 7	aman
Solution 8	Atish Satish Gosavi

'SQL Server: Multiple table joins with a WHERE clause

Solution 1:[1]

Solution 2:[2]

Solution 3:[3]

Solution 4:[4]

Solution 5:[5]

Solution 6:[6]

Solution 7:[7]

Solution 8:[8]