Finding ID having all values (mySQL, SQL)

Question

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

Answer 1

This would be my way to achieve it:

SELECT DISTINCT id
FROM table 
WHERE 
id IN (SELECT id FROM table WHERE value = 2)
AND 
id IN (SELECT id FROM table WHERE value = 5)

Answer 2

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

Answer 3

Try something like this:

SELECT id
  FROM test
WHERE 
   value in (2,5)
GROUP by id
HAVING count(*) = 2

If you want to test it, simple table for it (with no indexes!):

CREATE TABLE IF NOT EXISTS test (
  id int(4) NOT NULL,
  `value` int(4) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO test (id, value) VALUES
(100, 1),
(100, 3),
(101, 1),
(101, 4),
(102, 2),
(102, 5),
(103, 2),
(103, 4),
(104, 1),
(104, 3),
(105, 2),
(105, 5);

I had a very similar question a few days ago. see http://stackoverflow.com/questions/1242223/mysql-find-rows-matching-all-rows-from-joined-table

Answer 4

Hey,

I have a small follow up question. I have a familiar problem, but the queries above only give one line as a result. I have a table like

ID  Value
1     4
1     4
1     5
2     4
3     5

I want both equal lines lines(!), like

 ID Value
 1    4
 1    4

Thanks a lot!