SQL Server:具有WHERE子句的多个表联接 [英] SQL Server: Multiple table joins with a WHERE clause

问题描述

我正在使用SQL Server，但在尝试从所需的SELECT查询中获取结果时遇到了困难.我尝试以不同的顺序加入并使用子查询，但没有任何一种方法可以按我的方式工作.以这个人为设计的软件应用程序示例为例，它们可能会安装在人们的计算机上，具有不同的版本级别.

我需要对WHERE执行JOIN，但是由于某些原因，我无法获得所需的结果.

也许我看错了我的数据，我不太确定为什么我无法使它正常工作.

应用表

ID  Name
1   Word
2   Excel
3   Powerpoint

软件表(包含不同应用程序的版本信息)

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

Software_Computer 关联表

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

计算机表

ID  ComputerName
1   Name1
2   Name2

我想要一个查询，我可以在其中选择一台特定计算机的地方运行，以显示所拥有的软件版本和应用程序，但是我还希望它显示其所不具有的应用程序(该版本为NULL，因为它没有该软件)

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 

我想要以下结果集

ComputerName   Name          Version
Name1          Word          2003
Name1          Excel         2007
Name1          Powerpoint    NULL

但是我得到

Results
ComputerName   Name          Version
Name1          Word           2003
Name1          Excel          2007

我认为RIGHT JOIN会将所有结果包括在应用程序表中，即使它们与计算机无关.我在想什么/做错了什么?

解决方案

在使用LEFT JOIN或RIGHT JOIN时，将过滤器放在WHERE还是JOIN中都会有所不同.

请看我前段时间写的一个类似问题的答案:
由于获得两个不同的结果集，这两个查询有何区别?

简而言之:

• 如果将其放入WHERE子句中(就像您所做的那样，与该计算机无关的结果将被完全过滤掉
• 如果将其放入JOIN中，则与该计算机不相关的结果将显示在查询结果中，仅具有NULL值
  ->这就是你想要的

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?

解决方案

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

这篇关于SQL Server:具有WHERE子句的多个表联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！