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

Thank you guys, I started to test queries to find the fastest and the lightest one. It seems that Rufinus's query is the fastest. 20,000 records, 1006 results Jhonny average 0.0045 seconds David average 0.0020 seconds Rufinus average 0.0010 seconds (I'd never think doing in this way :)) I am open to new suggestions — Ergec
– Ergec, Commented Aug 10, 2009 at 14:48
Even tough Rufinus' answer is the fastest I'd select David's answer. Its fast enough and flexible so I can group values with ANDs and ORs — Ergec
– Ergec, Commented Aug 11, 2009 at 15:02

Community · Accepted Answer · 2017-05-23 12:07:10Z

7

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 MySQL - Find rows matching all rows from joined table

edited May 23, 2017 at 12:07

CommunityBot

11 silver badge

answered Aug 10, 2009 at 14:11

Rufinus

30.9k7 gold badges71 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

David · Accepted Answer · 2009-08-10 13:44:15Z

1

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

answered Aug 10, 2009 at 13:44

David

5,5062 gold badges29 silver badges39 bronze badges

Comments

Jhonny D. Cano -Leftware- · Accepted Answer · 2009-08-10 13:30:24Z

0

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)

answered Aug 10, 2009 at 13:30

Jhonny D. Cano -Leftware-

18.1k15 gold badges84 silver badges103 bronze badges

Collectives™ on Stack Overflow

Finding ID having all values (mySQL, SQL)

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related