I have 3 tables as such
|Product ID||Product Details|
|Vendor ID||Vendor Details|
|Product ID||Vendor ID|
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
For the above data, the expected results would be
Not Mapped Products
|Vendor ID||Product Count|
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 exists and 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 )
If you know for sure that a product will always be properly linked to a vendor, you could use an INNER JOIN, and simply filter out results where vender_id = 3.
SELECT COUNT(*) AS product_count FROM products AS pr INNER JOIN vendor_product_map AS vp ON pr.id = vp.product WHERE vp.vendor <> 3
If vendor might ever be NULL, and you want those results as well, you’d want to stick with a LEFT JOIN, like this…
SELECT COUNT(*) AS product_count FROM products AS pr LEFT JOIN vendor_product_map AS vp ON pr.id = vp.product WHERE vp.vendor <> 3 OR vp.vendor IS NULL