Bogdan Ciulei Bogdan Ciulei - 5 months ago 8
MySQL Question

Selecting against subsets of a list in MySQL

I'm quite a begginer and I have two tables: "product" and "product attributes".

Here's some imaginary data (the actual stuff involves more tables )

Products Table:

product_id | product_name
10 | aaa
11 | bbb
12 | ccc


Product Attributes Table:

attribute_id | product_id
21 | 10
23 | 10
24 | 10
21 | 11
24 | 11
21 | 12
25 | 12


Where each product has more than one possible attribute. I have a list of attribute ids like
(21,10,25)
and I need to select all products whose attributes are a subset of that list.

Is it possible to do this in one query?

When I filter for (21,24) desired output is to return only product 11 (bbb)

When I filter for (21,23,24) desired output is to return products 10 and 11.

When I filter for (21) desired output is to return none (because all products have at least one other attribute).

Answer

If you pretend that your filter is in a table:

select * 
from product p
where not exists (
    select 1
    from attributes a
    where a.product_id = p.product_id
    and not exists(
        select 1
        from filter f
        where f.id_attribute = a.id_attribute))

If it was in a constructed query:

select * 
from product p
where not exists (
    select 1
    from attributes a
    where a.product_id = p.product_id
    and attribute_id not in (<list>))

This is off the top of my head, so may have typos.