Home > Enterprise >  Get count of unmapped records when using a mapping table
Get count of unmapped records when using a mapping table

Time:03-03

I have 3 tables as such

Product

Product ID Product Details
1 ...
2 ...
3 ...

Vendor

Vendor ID Vendor Details
1 ...
2 ...
3 ...

Product Vendors

Product ID Vendor ID
1 1
2 1
1 2
2 2
3 2

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
)
  • Related