Home SQL Query result with contains values from an array
Reply: 1

SQL Query result with contains values from an array

Korty
1#
Korty Published in 2017-11-14 10:11:52Z

I search a MySQL solution to collect queries, which contains some ints (ids in an array).

Example-Table:

id1 id2 id3
1   2   2
2   3   2
3   3   5
4   4   2
5   4   5
6   4   7

I have an array like

$id3array = (2,5);

I need id2 = 3 (not 4, because id2 "4" has one more id3 argument (7)

If I have an array like

$id3array = (2,5,6); // array can contains 20 and more arguments

In this example Table I need id2 = 0 (because no match)

My Tests with IN(2,5) or Group by was not successful - Is this possible to solve with MySQL?

http://www.sqlfiddle.com/#!9/b71306/2

B. Desai
2#
B. Desai Reply to 2017-11-14 10:40:21Z

Try following query. Put inner query which will match records which is not in desired array. Then group and count exact element

SELECT `id2` FROM `exampletable` WHERE `id3` IN (2,5) AND `id2` NOT IN (
  SELECT e.id2 from exampletable e WHERE e.id3 not in (2,5))  GROUP BY `id2` 
HAVING count(`id2`) = 2;

Here I have added count(id2) = 2 because you have 2 elements in array

DEMO

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.315323 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO