I have 3 tables as such

Product

Vendor

ProductVendors

How would I go about finding the number of products that are not mapped to a specific vendor.

I tried:

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

but that doesn't seem right. Any help is appreciated

CodePudding user response：

A simple not exists query should be sufficient:

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