Page 1 of 1
Need SQL Queries to list categories with no products and Products not in a Category
Posted: Mon Jun 26, 2023 6:08 pm
by 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.
Re: Need SQL Queries to list categories with no products and Products not in a Category
Posted: Thu Jun 29, 2023 4:34 pm
by 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);
. 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);
Re: Need SQL Queries to list categories with no products and Products not in a Category
Posted: Thu Jun 29, 2023 6:12 pm
by lecarlb
burt wrote: ↑Thu Jun 29, 2023 4:34 pm
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);
. 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.
Re: Need SQL Queries to list categories with no products and Products not in a Category
Posted: Thu Jun 29, 2023 10:34 pm
by 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