将缺失的子集查找到集合中

Question

我有 3 个表：

cert         sct           server
========    =========    ============
cert_id     cert_id        serv_id
pem         serv_id        url
date_added  load           
type

基本上，每次我得到一个新的证书(表cert(，我都会把它提交给不同的服务器(在表server中用于时间戳，并将响应保存在表sct中。由于某种原因，我可能有一些未提交的证书。

此外，只能提交sign类证书。 所以我需要重新提交它们。

为此，我需要查找尚未提交到特定服务器的证书。

cert
======
cert_id       pem      date_added      type
--------------------------------------------
idCert1      (data)      (date)        sign
idCert2      (data)      (date)        email
idCert3      (data)      (date)        sign
idCert4      (data)      (date)        sign
idCert5      (data)      (date)        email

sct
====
cert_id        serv_id         load
------------------------------------
idCert1        serv1           (data)
idCert1        serv2           (data)
idCert3        serv1           (data)
server
======
serv_id       url
--------------------
serv1         url1
serv2         url2

假设这些记录，我想要这个结果：

cert_id     pem      serv_id         url
-----------------------------------------
idCert3     (data)   serv2           URL2
idCert4     (data)   serv1           URL1
idCert4     (data)   serv2           URL2

到目前为止，我能做的最好的事情是：

SELECT * 
FROM cert 
WHERE type='sign' 
AND cert_id NOT IN (SELECT cert_id 
FROM sct 
GROUP BY cert_id 
HAVING COUNT(cert_id)>=nbr_srv );

其中nbr_srv是表server中的服务器总数。该查询仅为我提供尚未提交到所有服务器的证书。 我不知道是否有可能通过单个查询获得我想要的东西。

Answer 1

demo：db<>fiddle

在所有可能的服务器/证书组合上生成交叉联接，并删除实际存在的组合：

SELECT
*
FROM
cert c
CROSS JOIN serv s
WHERE c.type = 'sign' AND (c.id, s.id) NOT IN (
SELECT
cert_id, serv_id
FROM
sct sc
)