Looking for MySQL/MariaDB where query over muliple rows-CodePudding

Table Products
ad_id| property_id | property_value_id
69          4           1
69          7           6
69          6           3
67          7           6
...

For Example:

I need to search ((where property_id = 4 and property_value_id = 1) and (where property_id = 7 and property_value_id = 6))

Result should be: 69

The values im searching for are dynamic.

CodePudding user response：

SELECT DISTINCT ad_id
FROM
    (SELECT ad_id
    FROM Producst
    WHERE (property_id = 4 AND property_value_id = 1)
      OR  (property_id = 7 AND property_value_id = 6)
      OR  (property_id = 6 AND property_value_id = 3)
    group by ad_id
    having count(ad_id) = 3) as subquery

Should do the job.

CodePudding user response：

You can join the table with itself three times, one for each pair of searched value. The query can take the form:

select distinct a.ad_id
from my_table a
join my_table b on b.ad_id = a.ad_id
join my_table c on c.ad_id = a.ad_id
where a.property_id = 4 and a.property_value_id = 1
   or b.property_id = 7 and b.property_value_id = 6
   or c.property_id = 6 and c.property_value_id = 3