Geek Answers Handbook

Search for an identifier that has all the values ​​(mySQL, SQL)

Think of such a table

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 that if I give values ​​2 and 5, I have to get 102 and 105, which at the same time 102 and 105 have values ​​2 and 5 at the same time, or if I give values ​​1 and 3, I have to get 100 and 104.

How can I do this with just one sql command? I need a request as easy as possible.

and using UNION, INTERSECTION or NESTED SQL, which are faster, slower, heavier etc?

Thanks in advance Ergec

+4
source share
3 answers

There are many solutions. Addition to Jhonny solution, you can use connection

 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
0
source

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, a simple table for it (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 a joined table

+5
source

That would be my way to achieve this:

 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)
0
source

More articles:


All Articles