tags:

views:

117

answers:

7

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

+1  A: 

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))
Eric J.
yes but that would select also products having one attribute in the list and the other not in the list . I have already done it like that. I need them to have all attributes in the list ( like I said ), so every possibly row from attribute table with with that product_id
Bogdan Ciulei
+1  A: 
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;
Qwerty
this solution also seems to work except it return more rows per product so it would benefit a group by P.id_product in itedit :no , it doesnt work.. same problem, it doesn't filter all attributes of a product, it 's enough for one of them to be in the list
Bogdan Ciulei
yes but there 's also the other problem that it doesn't filter all the attributes
Bogdan Ciulei
I misunderstood you first. Looks like fixed now?
Qwerty
nop, sorry, stil doesn't work the way I want
Bogdan Ciulei
look in my second post for sample data
Bogdan Ciulei
OK. updated queries
Qwerty
Hmm, no, that's not correct :)
Qwerty
yes, this works, thanks, an easier answer was posted but I checked all
Bogdan Ciulei
too late, but I've posted working version :)I guess it's the simpliest of all (and maybe the fastest)
Qwerty
yes, it works fine thakns.. the profiler return 0.000004 seconds execution time for all methods, of course I need larger sets of data
Bogdan Ciulei
A: 

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
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)
or when I filter for (21,23,24 ) to be returned both products
or when I filter for (21) only to be returned none ( because no product has only that attribute )

Bogdan Ciulei
this stuff is in prestashop , if you need a module to filter by attributes, but more complex tables and relations
Bogdan Ciulei
I would suggest that you edit the question and add this to the question.
Phil Wallach
This kind of information should be included in your original question. It's possible to edit your question if you want to later include more information. I've edited your question for you.
Mark E
+1  A: 

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
this doesn't work in case (21,23,24) (selects only 1 product )
Bogdan Ciulei
I think the revised query should work - the first inner select should generate 10,3 11,2 and the second inner select should generate 10,3 11,2 and so the outer select should match on both 10 and 11.
yes, now it work, an easier way was posted but you stil get a vote
Bogdan Ciulei
thanks for your support
Bogdan Ciulei
I think the answer that uses the "not in list" approach will incorrectly return product id's if those id's happen to have no attributes.
you are right, but at least two attributes per product is the way the script is designed. I forgot to specify that
Bogdan Ciulei
+1  A: 

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.

Phil Wallach
yes, the second one works, thank you
Bogdan Ciulei
+1  A: 

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.

outis
same, doesn't work in case (21,23,24) according to my example
Bogdan Ciulei
@Bogdan: original code was for question revision 3. Update to match current revision (7).
outis
I did not modify the question, just explained it better with examples. Thanks for the effort anyway, you got a vote from me
Bogdan Ciulei
@Bogdan: the question was ambiguous, but no longer. Clarifying is modification.
outis
A: 

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
Bogdan Ciulei