There is a table cs_goods_features like this (id - product id, f - product property name, v - property value)

I need to select only those products id, which have, for example, f1 = 10 and f2 = 15.

If I make a query like this

SELECT id 
  FROM cs_goods_features
  where (f in ('f1', 'f2'))
    and (v in (10,15))

then everything is fine except when the table has opposite values - not f1=10 and f2=15, but f1=15 and f2=10. I don't need such lines in result set.

What I need can be done like this:

select g1.id, g2.id
   FROM cs_goods_features g1, cs_goods_features g2 
 WHERE g1.f = 'f1'
   and g1.v = 10 
   and g2.f = 'f2'
   and g2.v = 15

But the problem here is that I get two columns in the output (and if i need to select products by 3 properties - there would be 3 columns). And I'm not satisfied with it, because actually this query is going to be a subquery in a larger query.

In other words there will be

SELECT * 
  FROM tablename
 where ID in (our_query)

It means I need exactly one column with results for construction "where ID in (...)" to work correctly

CodePudding user response：

One way to do it is by counting for each id the number of times (f1, 10) and (f2, 15) appear and selecting distinct id where they appear at least once each:

select distinct id from
(select *,
sum(case when f = 'f1' and v = 10 then 1 else 0 end) over(partition by id) as f1_10,
sum(case when f = 'f2' and v = 15 then 1 else 0 end) over(partition by id) as f2_15
from cs_goods_features) t
where f1_10 > 0 and f2_15 > 0

Fiddle

CodePudding user response：

I think you just separate into 2 groups like below:

SELECT id
  FROM cs_goods_features
 WHERE (f = 'f1' AND v = 10)
    OR (f = 'f2' AND v = 15)