获取多对多表中未映射记录的计数

Question

我有 3 个表，例如

Product

ProductID	ProductDetails
1	...
2	...
3	...

Vendor

VendorID	VendorDetails
1	...
2	...
3	...

ProductVendors

ProductID	VendorID
1	1
2	1
1	2
2	2
3	2

我如何查找未映射到特定供应商的产品数量。

我尝试过：

SELECT
    COUNT(pr.id) AS product_count
FROM
    products pr
LEFT JOIN vendor_product_map vp ON
    pr.id = vp.product
LEFT JOIN vendors vv ON
    vp.vendor = vv.id
WHERE
    vv.id = 3 AND vp.vendor IS NULL

但这似乎不对。任何帮助表示赞赏

Answer 1

一个简单的 not contains 查询就足够了：

select *
from products
where not exists (
    select *
    from vendor_product_map
    where vendor_product_map.product = product.id
    and vendor_product_map.vendor = 12345678
)