Get count of unmapped records when using a mapping table
I have 3 tables as such
Product ID Product Details 1 ... 2 ... 3 ...
Vendor ID Vendor Details 1 ... 2 ... 3 ...
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.
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
Edit: For the above data, the expected results would be
Not Mapped Products
Vendor ID Product Count 1 1 2 0 3 3
In short not mapped product count = total products - count of mapped products for a specific vendor, I'll be specifying the vendor ID so the result set will have only 1 row. Hope that clarifies.
I got it to work using
not existsand a subquery.
SELECT COUNT(pr.id) AS product_count FROM products pr WHERE NOT EXISTS( SELECT * FROM vendor_product_map vp WHERE vp.product = pr.id AND vp.vendor = 3 )