SQL Select statement construction

Given a table T with

|rowid| fld1 | fld2|
|1    |  1   | 10  |
|2    |  1   | 20  |
|3    |  2   | 10  |
|4    |  3   | 20  |

if I want to get all the fld1 values with fld2 = 10 AND fld2 = 20.

This would only be fld1 - the only one with fld2 values 10 and 20.

Is my best way to go a开发者_如何学Go subquery:

select * from T where fld2 = 10 and rowid in (select rowid from T where fld2 = 20); 

or is there a better query?

You can try a self-join:

select * 
from T t1 JOIN T t2 on t1.fld1 = t2.fld1
where t1.fld2 = 10 
and t2.fld2 = 20

I not sure on the relative performance of this vs @nick's solution

I hope I have understood your question

select * from table where fld1 in (
select fld1 from table
where fld2 in (10,20)
group by fld1
having count(distinct(fld2)) = 2)

If you don't need to retrieve all rows

select fld1 from table
where fld2 in (10,20)
group by fld1
having count(distinct(fld2)) = 2

this query suffices.

SELECT * FROM T WHERE fld2 = '10' AND fld='20'

It really is that simple.