CODEWITHSUNDEEP
mysqlselect
Bogdan Ciulei
Bogdan Ciulei

Reputation: 91

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).

Upvotes: 9

Views: 4493

Answers (7)

Bogdan Ciulei
Bogdan Ciulei

Reputation: 91

let me post simple imaginary data ( the actual stuff involves more tables )

table products

product_id | product_name                  
10          |   aaa                           
11     |    bbb

table product_attribute 

attribute_id | product_id  <br>
21     |    10         
23     |    10         
24     |    10         
21     |    11         
24     |    11

i want that:

  • when I filter for (21,24) to be returned only product 11 (bbb)
  • when I filter for (21,23,24) to be returned both products
  • when I filter for (21) only to be returned none ( because no product has only that attribute )

Upvotes: -1

outis
outis

Reputation: 77400

Until MySQL supports the EXCEPT query combination,

SELECT product_id
  FROM attributes
  WHERE product_id NOT IN (
       SELECT product_id
         FROM attributes 
         WHERE attribute_id NOT IN (21, 23, 24)
     )
  GROUP BY product_id
UNION
SELECT id 
  FROM products AS p
  LEFT JOIN attributes AS a
    ON p.id = a.product_id
  WHERE a.product_id IS NULL

If you wish to have only the products with all the given attributes, add a HAVING COUNT(*)=n clause to the first outer query, where 'n' is the length of the attribute list.

Upvotes: 1

Bogdan Ciulei
Bogdan Ciulei

Reputation: 91

Based on your insight guys, I optimized it even further and used only 1 COUNT statement like this:

SELECT * ,COUNT(p.product_id) AS c FROM product_attribute pa 
LEFT JOIN products p ON pa.product_id = p.product_id AND pa.attribute_id NOT IN ($filter_list)
GROUP BY pa.product_id
HAVING c=0

Does it work ? :)

Edit:
That code doesn't return the product's name or other fields it might have. This is the correct one:

SELECT * ,COUNT(pa.product_id ) AS c FROM products p
LEFT JOIN product_attribute pa ON pa.product_id = p.product_id AND pa.attribute_id NOT IN ($filter)
GROUP BY p.product_id
HAVING c=0

Upvotes: 0

Qwerty
Qwerty

Reputation: 1742

select
    P.id,
    P.name,
    count(P.id) as matched_attr_count,
    count(PA.a_id) as total_attr_count
from
    product_attributes PA
    left join product P on P.id = PA.p_id and PA.a_id in (21,23,24)
group by
    PA.p_id
having
    matched_attr_count = total_attr_count;

Upvotes: 1

user273224
user273224

Reputation: 11

This should return only those id's where all attributes for each id are completely contained within the list:

select attribute_match.id_product from
 (select id_product, count(*) c from attributes
  where id_attribute in (21, 10, 25)
  group by id_product) attribute_match,
 (select id_product, count(*) c_count from attributes
  group by id_product) attribute_total
where attribute_match.id_product = attribute_total.id_product
      and attribute_match.c = attribute_total.c

Upvotes: 1

Phil Wallach
Phil Wallach

Reputation: 3318

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.

Upvotes: 5

Eric J.
Eric J.

Reputation: 150108

Assuming your product table is called Product and the ID column in that table is just called Id:

SELECT * from Product p where p.Id IN 
  (Select id_product from ProductAttributes where id_attribute in (21, 23, 24))

Upvotes: 1

Related Questions