Finding ID having all values (mySQL, SQL)

mysql, sql

Solution

There's lots of solutions. Addition to the Jhonny's solution, you can use a join

SELECT DISTINCT t1.id
FROM table t1, table t2
WHERE t1.id = t2.id
  AND t1.value = 2
  AND t2.value = 5

OR use Intersect

SELECT id FROM table WHERE value = 2

INTERSECT

SELECT id FROM table WHERE value = 5

Problem

Think about a table like this ``` ID Value 100 1 100 3 101 1 101 4 102 2 102 5 103 2 103 4 104 1 104 3 105 2 105 5 ``` The problem is, if I give values 2 and 5 I should get 102 and 105 which both 102 and 105 are having values 2 and 5 at the same time or if I give values 1 and 3 I should get 100 and 104. How can I do this with only one sql command? I need a query as light as possible. and using UNION, INTERSECTION or NESTED SQLs, which one is faster, slower, heavier e.t.c? Thanks in advance Ergec

Original source