There are two tables: Good(name, id) and GoodHasAttribute(good_id, attribute_id, value) . The GoodHasAttribute table stores attribute values ​​for each item. I need to extract products with the specified properties. There is currently the following query:

 SELECT * FROM Good t JOIN GoodHasAttribute goodAttributes ON (`goodAttributes`.`good_id`=`t`.`id`) and ( ((goodAttributes.attribute_id=1) AND (goodAttributes.value LIKE '%белый%')) or ((goodAttributes.attribute_id=3) AND (goodAttributes.value LIKE '%красный%')) )

It returns to me only those products in which there is at least one attribute, but I need with both.

So far, I have thought up to remove the grouping of conditions and or replace with and , but in this case I will also receive goods with attributes " attribute_id=1, value=красный " and " attribute_id=3, value=белый ".

Which query will retrieve the data I need?

  • one
    @ co11ter, something like select * from good t join GoodHasAttribute ga1 on t.id = ga1.good_id and ga2.attribute_id = 3 and ga2.value like '% red%'; - alexlz
  • @ co11ter, because one field (attribute_id) cannot simultaneously have the value 1 and 3. This means that you need different rows from the GoodHasAttribute, respectively, for a query, this is a table with different aliases. - alexlz

1 answer 1

two join should be used. like that:

SQL feeddle

MySQL 5.6 Schema Setup :

 create table g (id int, n text); insert into g values (1, "товар1") ,(2, "товар2") ; create table a (id int, g int, v text); insert into a values (1, 1, "белый") ,(3, 1, "красный") ,(1, 2, "серый") ,(3, 2, "зелёный") ;

Query 1 :

 select * from g join a a1 on a1.g = g.id and a1.id = 1 and a1.v like "%белый%" join a a2 on a2.g = g.id and a2.id = 3 and a2.v like "%красный%"

Results :

 | id | n | id | g | v | id | g | v | |----|--------|----|---|-------|----|---|---------| | 1 | товар1 | 1 | 1 | белый | 3 | 1 | красный |