How to Select * FROM table1, table2 correctly when table1 = table2

Question

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

id	f	v
1	f1	10
1	f2	15
1	f3	25
2	f1	10
2	f2	15
2	f3	35
3	f1	15
3	f2	10
3	f3	55

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

Answer 1

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)