Postgresql select

Question

My datatable:

[a] | [b]
----+----
1 | 1
1 | 2
1 | 3
2 | 1
2 | 2
3 | 1

What is the correct select for:

SELECT a FROM table WHERE b = 1 AND b = 2 AND b = 3 // Result = 1
SELECT a FROM table WHERE b = 1 AND b = 2 // Result = 2

EDIT: Thanks this query resolve my problem:

SELECT a FROM table WHERE b IN (1,2,3) AND a IN (SELECT a FROM table GROUP BY a HAVING count(*) = 3) GROUP BY a HAVING count(*) = 3 // Result = 1
SELECT a FROM table WHERE b IN (1,2) AND a IN (SELECT a FROM table GROUP BY a HAVING count(*) = 2) GROUP BY a HAVING count(*) = 2 // Result = 2

• Erwin Brandstetter: The question needs an exact table definition, your version of Postgres and a more descriptive title. Can columns be NULL? Is (a,b) unique? The table definition tells us all that and more: what you get with \d tbl in psql.

Answer 1

Not exactly clear what you're asking, but I think you're looking for EXISTS: http://www.postgresql.org/docs/9.4/static/functions-subquery.html

Depending on other constraints on your data, you may be able to do:

SELECT a FROM "table" WHERE b IN(1,2,3) GROUP BY a HAVING count(*) = 3

Answer 2

Since OP lacks some info:

select a from (
select a,row_number() over(partition by a) rn from foo 
where b in (1,2,3) )t
where rn=(select count(a) from foo where a =1) -- you can use `rn` =3 instead of `select count(a) from foo where a =1`
select a from (
select a,row_number() over(partition by a) rn from foo 
where b in (1,2) )t
where rn=(select count(a) from foo where a =2)-- you can use `rn` =2 instead of `select count(a) from foo where a =2`