SQL Server: Multiple table joins with a WHERE clause

Question

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?

Tweedy

Maybe it's cos I'm high, but aren't you constraining the Applications by the computers software? And aren't there just two software belonging to computer 1? Also, shouldn't a left join override a right join? That I need to check. And PowerPoint has a version so why would you expect null? Also, with multiple versions for applications, in the computers software table, you need to to maintain a version id as well, else you'd have all kinds of messed up results. – Actium

Answer 1

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

Dodiedodo

This seems to be what I was missing. I just tested it and it looks to be producing the results I want. Thank you. – Tweedy

The expected result is to have "name1" listed with the "powerpoint". Adding the "computer.ID = 1" to the join doesn't quite give the expected result. The row with the "powerpoint" value will have its computer name column be null value. Between w0lf's and Oleg's solutions, oleg is the better of the two. – Instantly

@Tweedy Please help me to understand, WHERE Computer.ID = 1 will filter the data after all the joins are performed. So ideally it should display Name1 Powerpoint NULL row. but why OP is not getting it ? – Flavorous

Answer 2

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

Touchline

Thank you. This is perfect! I was trying to accomplish too much with a single SELECT. I would upvote this if I could. – Tweedy

I knew that's what you needed; it was very clear from the question. I'm not sure why I got a downvote on this answer though. Anyway, I'm very glad it helped. :-) – Touchline

It is not as performant as Oleg's. And also, some minor issues I have with it are 1) UNION ALL because the sets are already mutually exclusive by definition 2) be more explicit with the CROSS JOIN – Instantly

Answer 3

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

Cursory

Answer 4

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 (+).

Verso

This does not work. The LEFT OUTER JOIN changes nothing; the intermediary result-set still have "name1" associated with "word" and "excel". The right outer join will still include "powerpoint" in the next result-set but associated with NULL value for the computer name. Then finally, the where condition removes the powerpoint record from the result-set because its null value for computer name <> "name1" – Instantly

Answer 5

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;

Bottleneck

This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post - you can always comment on your own posts, and once you have sufficient reputation you will be able to comment on any post. – Flavius

Answer 6

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 

Vancevancleave

Answer 7

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 

Straighten

Answer 8

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 ;

Zambia

Answer 9

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 

Mosesmosey

You may consider adding a few comments and explanatory sentences to your code as this increases the value of your answer for other users. – Linguistician