Home Get records from mySQL table that have certain ID set, and it's the only record in table

Questions

Get records from mySQL table that have certain ID set, and it's the only record in table

February 3, 2022

I have a database table that looks like that:

I want to pull all product_id from such table that have certain category_id set, AND this record is the only one for that product ID. (if there are two records for product_id with any category_id, it shouldn’t be included).

I tried something like that:

SELECT product_id 
FROM `products_categories` 
WHERE category_id = 541 
AND HAVING COUNT(product_id) = 1;

But that returns a syntax error that I can’t figure out. Anyone can advice what I may be doing wrong?

>Solution :

SELECT product_id 
FROM products_categories t1
WHERE category_id = 541 
AND NOT EXISTS ( SELECT NULL
                 FROM products_categories t2 
                 WHERE t1.product_id = t2.product_id 
                   AND t1.category_id <> t2.category_id );