Select by an IN clause but should behave like AND

Question

IN clause works like OR,

select from table where id in (1, 2, 3)

ie selects in case any of the item in group matches (returns if id is 1, 2 or 3).

I need something similar, but based on two columns, and it should return only if ColumnA has all the values of ColumnB.

For eg,

  ColumnA         ColumnB
---------------------------
    1               a
    2               b
    3               c

    1               a
    1               b
    1               c


select CoumnA from table where CoumnA in every ColumnB of (a, b, c);
 -> 1 --since only 1 has all a, b and c

select CoumnA from table where CoumnA in every ColumnB of (b);
 -> 1 --since 1 has b
 -> 2 --since 2 has b

And so on. I know in every of is not a proper keyword, I'm just trying to show an example.

I couldn't try anything since I couldn't get my head around this logic.

@Rikesh thanks, I will see that. Sometimes searching for duplicates is almost impossible for sql type questions. — IHashable, Jan 31 '14 at 07:08

score 4 · Accepted Answer · answered Jan 31 '14 at 07:06

4

Try using just IN(a,b,c) Group by id having count(distinct id) = 3.

answered Jan 31 '14 at 07:06

user3255962

81
2

score 0 · Answer 2 · answered Jan 31 '14 at 07:12

What about this:

select t1.ColumnA from table t1 where 
exists (select * from table t2 where t1.ColumnA=t2.ColumnA and t2.ColumnB='a')
and exists (select * from table t2 where t1.ColumnA=t2.ColumnA and t2.ColumnB='b')
and exists (select * from table t2 where t1.ColumnA=t2.ColumnA and t2.ColumnB='c')
group by t1.ColumnA

Select by an IN clause but should behave like AND

2 Answers2