I want to get the product id by 2 attributes, but mysql prints 0 lines. Example in DB

product_id 44 attribute_id 12 text 3 модуля product_id 44 attribute_id 13 text Π—Π΅Π»Π΅Π½Ρ‹ΠΉ

I make such a request

 AND ( ( pa.attribute_id = '13' AND pa.text = '1000x800 ΠΌΠΌ' ) AND ( pa.attribute_id = '14' AND pa.text = 'Π—Π΅Π»Π΅Π½Ρ‹ΠΉ' ) )

Displays 0 fields, if you enter only

  ( pa.attribute_id = '13' AND pa.text = '1000x800 ΠΌΠΌ' )

Displays the desired id. How to make the right request?

This is what the whole query looks like.

  SELECT p.product_id, ( SELECT AVG(rating) AS total FROM oc_review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id ) AS rating, ( SELECT price FROM oc_product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '1' AND pd2.quantity = '1' AND ( ( pd2.date_start = '0000-00-00' OR pd2.date_start < NOW() ) AND ( pd2.date_end = '0000-00-00' OR pd2.date_end > NOW() ) ) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1 ) AS discount, ( SELECT price FROM oc_product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '1' AND ( ( ps.date_start = '0000-00-00' OR ps.date_start < NOW() ) AND ( ps.date_end = '0000-00-00' OR ps.date_end > NOW() ) ) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1 ) AS special FROM oc_product_to_category p2c LEFT JOIN oc_product p ON (p2c.product_id = p.product_id) LEFT JOIN oc_product_attribute pa ON (pa.product_id = p.product_id) LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) LEFT JOIN oc_product_to_store p2s ON (p.product_id = p2s.product_id) WHERE pd.language_id = '1' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0' AND p2c.category_id = '46' AND ( ( pa.attribute_id = '12' AND pa.text = '5 ΠΌΠΎΠ΄ΡƒΠ»Π΅ΠΉ' ) OR ( pa.attribute_id = '13' AND pa.text = '1000x800 ΠΌΠΌ' ) OR ( pa.attribute_id = '14' AND pa.text = 'Π—Π΅Π»Π΅Π½Ρ‹ΠΉ' ) ) GROUP BY p.product_id ORDER BY p.sort_order ASC, LCASE(pd.name) ASC LIMIT 0, 9
  • show the whole request. where the union may not be true. - Kirill Korushkin

3 answers 3

 SELECT product_id FROM pa WHERE (attribute_id, text) IN (('13','1000x800 ΠΌΠΌ'),('14','Π—Π΅Π»Π΅Π½Ρ‹ΠΉ')) GROUP BY product_id HAVING COUNT(product_id) = 2
  • And what if the parameters are empty? WHERE (attribute_id, text) IN (('13', ''), ('14', 'Green')) - kosh
  • @kosh WHY do they arrive empty? What should happen if the value is not specified - choose any, just to be, or exactly what to look for empty? And yet - such things should have been thought out in advance and describe all possible options in the question itself. - Akina
  • Honestly, I did not think about it. The bottom line is that either the user selects one filter at a time, or all at once. And I can not figure out how to make this request correctly, as well as write a php script to it - kosh
  • The standard approach is usually: WHERE (value = @value OR @value = '') . True, it conflicts with WHERE .. IN (..) - the check will have to be transformed into comparison blocks according to the values ​​of individual fields ... although it is much more reasonable for such key-value pairs to be cut out on the client when forming the query calculate and insert into the body or parameter). - Akina
  • Thanks, I solved the problem, I just needed to add HAVING COUNT, in my query, and it worked - kosh
 select product_id from pa where (attribute_id = 13 and text = '1000x800 ΠΌΠΌ') or (attribute_id = 14 and text = 'Π—Π΅Π»Π΅Π½Ρ‹ΠΉ');

UPD:

 select distinct product_id from pa where attribute_id = 13 and text = '1000x800 ΠΌΠΌ' and exists ( select * from pa p where p.product_id = pa.product_id and p.attribute_id = 14 and p.text = 'Π—Π΅Π»Π΅Π½Ρ‹ΠΉ');

As one of the possible options.

  • Then he displays the id of the product, in which there is at least 1 parameter, this, but I need to display the product only for these two attributes simultaneously (I make the filter module on the site) - kosh
  • @kosh, I understand, I'll try right now. - DanielOlivo
  • Thank you, I will build on this - kosh
  • In general, this option is not rolled, if there is no first parameter (13), it will not output anything, thanks for the help anyway - kosh

The structure of the table and the meaning of the task of filtering the query by ID and text description of this ID, if they match, are not completely clear. I wrote an example on the sqlite dialect, I think it will not be difficult to correct it under mysql, but probably the required query will be like this:

 drop table if exists tab1; create table tab1 ( product_id int, attribute_id int, text text ); insert into tab1 select 44, 12, 'Π·Π΅Π»Π΅Π½Ρ‹ΠΉ'; insert into tab1 select 44, 13, '1000x800 ΠΌΠΌ'; insert into tab1 select 45, 12, 'Π·Π΅Π»Π΅Π½Ρ‹ΠΉ'; insert into tab1 select 46, 13, '1000x800 ΠΌΠΌ'; select product_id from tab1 tb1 where exists (select * from tab1 tb2 where tb1.[product_id] = tb2.product_id and tb2.[attribute_id] = 12 AND tb2.text = 'Π·Π΅Π»Π΅Π½Ρ‹ΠΉ' ) AND exists (select * from tab1 tb3 where tb1.[product_id] = tb3.product_id and tb3.[attribute_id] = 13 and tb3.text = '1000x800 ΠΌΠΌ' ) group by product_id

As a result, at the exit we get only goods with ID - 44:

 RecNo product_id ----- ---------- 1 44
  • Thanks, I'll try - kosh