SQL subsets query

Question

I am having trouble creating a query for an SQL table. The query I am trying to create shows the number of products within the category of "clothes" and does not show accessories for example a list of products that are entered as T-shirts or sweatshirts.

Here is the tables that have been created:

CREATE DATABASE IF NOT EXISTS product_list;

DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS product_categories;
DROP TABLE IF EXISTS categories;

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(50) DEFAULT NULL,
    active BOOL DEFAULT NULL
);

CREATE TABLE categories (
    category_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    structure VARCHAR(50)
);

CREATE TABLE product_categories (
    product_id INT,
    category_id INT,
    PRIMARY KEY(product_id, category_id)
);

INSERT INTO products VALUES
(NULL, "Blue Sweatshirt", false),
(NULL, "Short Sleeve T-Shirt", true),
(NULL, "White Vest", true),
(NULL, "Black Hairclip", true),
(NULL, "Knitted Hat", false),
(NULL, "Grey Sweatshirt", true),
(NULL, "Tartan Scarf", true);

INSERT INTO categories VALUES
(NULL, "Sweatshirts", "Clothes>Sweatshirts"),
(NULL, "T-Shirts", "Clothes>T-Shirts"),
(NULL, "Accessories", "Accessories"),
(NULL, "Winter", "Clothes>Winter"),
(NULL, "Vests", "Clothes>Vests");

INSERT INTO product_categories VALUES
(1, 1), (2, 2), (3, 5), (3, 4), (4, 3), (5, 3), (5, 4), (6, 1), (7, 3), (7, 4);

Answer 1

If I understand correctly, this is a set-within-sets query. You are looking for products that have at least one "clothes" category, and none of the categories are not clothes. I approach this using group by and having because it is quite flexible:

select pc.product_id
from Product_categories pc join
     categories c
     on pc.category_id = c.category_id
group by pc.product_id
having sum(case when c.structure like 'Clothes%' then 1 else 0 end) > 0 and
       sum(case when c.structure not like 'Clothes%' then 1 else 0 end) = 0;

If you just want the count, then you can use this as a subquery and use count(*).

EDIT:

A small note. The question is now tagged with MySQL, which has convenient short-hand for the having clause:

having sum(c.structure like 'Clothes%') > 0 and
       sum(c.structure not like 'Clothes%') = 0;

Answer 2

Try this query

select * from products a
join Product_categories b on a.product_id=b.product_id
join categories c on b.category_id=b.category_id
where c.name like '%Clothes%'