To retrieve records having only two specific values

Question

Have the following Data in the table

Example Table
ID  Value
1   a
1   b
1   c
2   a
2   b
2   c
3   a
3   b

I need to retrieve records having ID with only two values a and b. So i am expecting only the Record with ID 3 . Can anyone help me with the query

Please explain the problem more precisely. Exactly what do you mean "two values A and B"? One each? Two in any combination? — WarrenT, Nov 15 '13 at 00:56

score 0 · Answer 1 · answered Nov 14 '13 at 22:17

0

I guess you could do something like

select
ID,
 sum(case when value = 'a' then 1
when value = 'b' then 1
else 3 end)

from 
table1
group by id
having
sum (case when value = 'a' then 1
when value = 'b' then 1
else 3 end) =2

SQL Fiddle

answered Nov 14 '13 at 22:17

Andrew

8,445
3
28
46

score 0 · Answer 2 · answered Nov 14 '13 at 22:17

That will work:

select x.id from 
(
    select id from mytable where value = 'a'
    union all
    select id from mytable where value = 'b'
) x
group by x.id
having COUNT(*) = 2
and not exists (select * from mytable t where t.id = x.id and value <> 'a' and value <> 'b')

To retrieve records having only two specific values

2 Answers2