Need SQL Queries to list categories with no products and Products not in a Category

[lecarlb]

Hello,

I know they're in the old forum but I couldn't find them. Or if anyone here can help me find a way to sift through the products_to_categories table and weed out the products that are redundant or unnecessary.

I'm open to suggestions.

Thank you.

[burt]

List Categories with no products

Code: Select all

SELECT categories_id FROM categories where categories_id NOT IN (select categories_id from products_to_categories);

Be careful here. It is possible that a parent category only contains other categories, so this would show up as a category with no products. If you delete that parent category, it could cause your site a lot of hurt.

List Products not in a category

Code: Select all

SELECT products_id FROM products where products_id NOT IN (select products_id from products_to_categories);

[lecarlb]

List Categories with no products

Code: Select all

SELECT categories_id FROM categories where categories_id NOT IN (select categories_id from products_to_categories);

Be careful here. It is possible that a parent category only contains other categories, so this would show up as a category with no products. If you delete that parent category, it could cause your site a lot of hurt.

List Products not in a category

Code: Select all

SELECT products_id FROM products where products_id NOT IN (select products_id from products_to_categories);

Thank you. Your reply will be great for future reference.

[ecartz]

LEFT JOINs would be more efficient than NOT IN.

Code: Select all

SELECT p.products_id FROM products p LEFT JOIN products_to_categories p2c ON p.products_id = p2c.products_id WHERE p2c.categories_id IS NULL

Code: Select all

SELECT c.categories_id FROM categories c LEFT JOIN products_to_categories p2c ON c.categories_id = p2c.categories_id LEFT JOIN categories p ON c.categories_id = p.parent_id WHERE p.categories_id IS NULL AND p2c.products_id IS NULL