ms-access - 复杂的 MS Access 不在类型查询中

Question

我正在尝试编写一个返回与以下查询相反的文字的查询。

共有三个表。 Assets 、软件、软件安装。



<h2>ASSETS SCHEMA</h2>

<p>ID (Auto)
Hostname (Text)</p>

<h2>ASSETS DATA</h2>

<p>1,"PC1"
2,"PC2"
3,"PC3"
4,"PC4"
5,"PC5"
6,"PC6"
7,"PC7"
8,"PC8"</p>

<h2>SOFTWARE SCHEMA</h2>

<p>ID (Auto)
Title (Text)</p>

<h2>SOFTWARE DATA</h2>

<p>1,"Office"
2,"Project"
3,"Visio"
4,"Visual Studio"
5,"Doors"</p>

<h2>SOFTWAREINSTALLS SCHEMA</h2>

<p>fkAssetID (Number)
fkSoftwareID (Number)</p>

<h2>SOFTWAREINSTALLS DATA</h2>

<p>1,1
2,1
3,1
4,1
5,1
6,1
7,1
1,2
2,2
3,2
4,2
5,3
6,3
7,3
4,4
5,4</p>

<h2>Relationships</h2>

<p>Assets and Software have one-to-many from ID to the respective fk in SoftwareInstalls</p>

<h2>Query to return all software installed on each asset:</h2>

<p>SELECT Assets.[Hostname], Software.Title
FROM Software INNER JOIN (Assets INNER JOIN SoftwareInstalls ON Assets.ID = SoftwareInstalls.fkAssetID) ON Software.ID = SoftwareInstalls.fkSoftwareID
ORDER BY Assets.[Hostname];</p>

<h2>Returns:</h2>



"PC1","Project"
"PC1","Office"
"PC2","Office"
"PC2","Project"
"PC3","Project"
"PC3","Office"
"PC4","Project"
"PC4","Office"
"PC4","Visual Studio"
"PC5","Visio"
"PC5","Visual Studio"
"PC5","Office"
"PC6","Visio"
"PC6","Office"
"PC7","Visio"
"PC7","Office"


我想设计一个查询，返回与上面完全相反的数据，这样我们就可以知道哪些 Assets 没有安装哪些软件。



<h2>Desired query data:</h2>



"PC1","Visio"
"PC1","Visual Studio"
"PC1","Doors"
"PC2","Visio"
"PC2","Visual Studio"
"PC2","Doors"
"PC3","Visio"
"PC3","Visual Studio"
"PC3","Doors"
"PC4","Visio"
"PC4","Doors"
"PC5","Project"
"PC5","Doors"
"PC6","Project"
"PC6","Doors"
"PC6","Visual Studio"
"PC7","Project"
"PC7","Doors"
"PC7","Visual Studio"
"PC8","Project"
"PC8","Visio"
"PC8","Doors"
"PC8","Visual Studio"
"PC8","Office"


在 SQL 中，我能想到的最多的是针对具有所有软件的单台计算机或针对所有计算机的单一软件的“NOT IN”子查询。

在 VBA 中，这可以用几行代码和两个循环来完成。我想不出如何最好地将其作为查询/ View 合并到数据库中。

谢谢，

吉姆

Answer 1

我们不会使用 NOT IN 语句，但会使用计数检查它是否不在 SoftwareInstalls 中。

我们首先进行交叉连接，选择主机名和软件标题之间的所有可能组合，然后使用 where 语句检查主机名和软件标题之间的组合是否不在 SoftwareInstalls 表中(Count = 0，您可以将其重写为 NOT IN 语句，但它运行良好)。

SELECT a.Hostname, s.Title
FROM Assets a, Software s
WHERE (SELECT Count(fkAssetID) FROM SoftwareInstalls i WHERE i.fkAssetID = a.ID AND i.fkSoftwareID = s.ID) = 0