复杂MS访问不在类型查询中

Question

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

有三张桌子。资产，软件，SoftwareInstalls。

ASSETS SCHEMA

ID (Auto) Hostname (Text)

ASSETS DATA

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

SOFTWARE SCHEMA

ID (Auto) Title (Text)

SOFTWARE DATA

1,"Office" 2,"Project" 3,"Visio" 4,"Visual Studio" 5,"Doors"

SOFTWAREINSTALLS SCHEMA

fkAssetID (Number) fkSoftwareID (Number)

SOFTWAREINSTALLS DATA

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

Relationships

Assets and Software have one-to-many from ID to the respective fk in SoftwareInstalls

Query to return all software installed on each asset:

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];

Returns:

"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"

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

​

Desired query data:

"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“子查询，用于一台具有所有软件或用于所有计算机的单一软件的子查询。

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

谢谢,

吉姆

Answer 1

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

我们首先进行交叉连接，选择主机名和软件标题之间的所有可能组合，然后使用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

Answer 2

我不认为"IN“是你想在这里采取的方法。尝试使用NOT存在将您的结果集与另一个列表进行比较。这个例子应该会给你你想要的东西：

SELECT lst.*
  FROM
        (SELECT Assets.Asset_ID, Assets.Hostname, Software.Software_ID, Software.Title
        FROM Assets, Software ) lst
  WHERE NOT EXISTS (SELECT 1 
                     FROM softwareinstalls si 
                     WHERE si.Asset_id = lst.Asset_Id and si.Software_Id = lst.Software_Id)

下面是查询的工作方式(我重命名了所有ID字段以匹配表名)。

第一个子查询，lst，通过交叉连接资产表和软件表，构建了所有可能的资产和软件组合的列表。

接下来，我们将这个列表与安装列表进行比较，要求它返回所有在Asset_Id或Software_Id上都不匹配的内容。

不存在的coudl替换为左联接和“NULL"，但我认为这读起来更容易一些。