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
)