how can i select key that contains 2 value in talbe-CodePudding

My table:

|id |value|
| 1 |  a  |
| 1 |  b  |
| 1 |  c  |
| 2 |  b  |
| 3 |  a  |
| 3 |  d  |
| 3 |  c  |

I want to get result like this:

|id |
| 1 |
| 3 |

whose id contain a and c values. Please help me.

CodePudding user response：

Aggregation is one simple approach:

SELECT id
FROM yourTable
WHERE value IN ('a', 'c')
GROUP BY id
HAVING MIN(value) <> MAX(value);

The HAVING clause asserts that there are two different values present in each matching group after the WHERE clause has filtered off all values other than a and c. If the HAVING clause fails, then it means that both a and c are not present.

CodePudding user response：

Other approach:

select id       
from   yourtable    
group by id     
having sum(case when value in('a','c') then 1 else 0 end ) = 2;