MySQL select all elements in array

Question

I have this array:

$results = array('1', '3', '123')

and this table:

id  |  item  |  tag
1      1        1
2      1        3
3      1        123
4      2        1
5      3        1
6      4        3

I want to perform a query like this:

SELECT item FROM table WHERE ALL 3 TAGS OF THE ARRAY EXIST.

In the example I want to return only Item=1, because it's the only Item with all 3 tags of the array.

So far I have this:

$query .= " SELECT item FROM #__table WHERE tag IN ('";
$query .= implode("','",$results); 
$query .= "')";

But it does not return the correct Items, instead it returns all items that are related at least with one element of the array.

What does it return? Do you get an error? Or "just" an empty result? — Dan Soap
– Dan Soap, Commented Feb 15, 2012 at 21:39
Do you really wish for them to match simply by them existing, or should your array be logically related to each of the table columns? (item, id, tag) — hexparrot
– hexparrot, Commented Feb 15, 2012 at 21:41
@Cassy It just returns all items that have at least one of the elements of the array. — Yannis
– Yannis, Commented Feb 15, 2012 at 21:42
@hexparrot I am sorry I don't understand your question. I want to return only the items that are related in the table with all 3 tags in the array. — Yannis
– Yannis, Commented Feb 15, 2012 at 21:43

Joe Stefanelli · Accepted Answer · 2012-02-15 21:44:11Z

6

To make the IN work as an AND instead of an OR, you use a HAVING clause to make sure that you get the same number of distinct values returned as exist in the IN clause.

SELECT item
    FROM YourTable
    WHERE tag IN ('1', '3', '123')
    GROUP BY item
    HAVING COUNT(DISTINCT tag) = 3

answered Feb 15, 2012 at 21:44

Joe Stefanelli

136k21 gold badges243 silver badges241 bronze badges

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

3 Comments

Yannis Over a year ago

What if the count of the elements in the array is dynamic and not always 3? Should it be HAVING COUNT(DISTINCT tag) = count($results) ?

Joe Stefanelli Over a year ago

@yann: The value you test in the HAVING clause must be equal to the number of elements in the array.

Marcus Adams Over a year ago

If there are no duplicate tags for a particular item (in other words, if there is a unique constraint on (item, tag)), then you don't need DISTINCT.

Marc B · Accepted Answer · 2012-02-15 21:43:52Z

4

An IN clause is basically just a shortcut for writing out a long or chain, e.g.

... WHERE a IN (1,2,3)

is the same as

... WHERE (a = 1) or (a = 2) or (a = 3)

If you require that ALL of the values in the IN clause be present, you can't use a basic IN clause. There's no WHERE IN ALL (...) type construct in MySQL.

You can simulate it with a subquery:

SELECT *
FROM yourtable
WHERE id IN (
    SELECT DISTINCT id
    FROM yourtable
    WHERE tag IN (1,3,123)
    GROUP BY id
    HAVING (COUNT(tag) = 3)
);

The inner query fetches all IDs where all three tag values are present, then the outer query uses the inner results to fetch the full record.

answered Feb 15, 2012 at 21:43

Marc B

362k44 gold badges433 silver badges508 bronze badges

1 Comment

Marcus Adams Over a year ago

Shouldn't the inner query group on item, not, id?

Steve · Accepted Answer · 2012-02-15 21:50:31Z

1

SQL:

SELECT item FROM table WHERE tag IN (1,3,123) GROUP BY item HAVING COUNT(DISTINCT tag) = 3;

PHP:

$query = sprintf('SELECT item FROM table WHERE tag IN (%s) GROUP BY item HAVING COUNT(DISTINCT tag) = %d',
    implode(',', $results),
    count($results));

answered Feb 15, 2012 at 21:50

Steve

7,1683 gold badges47 silver badges42 bronze badges

Collectives™ on Stack Overflow

MySQL select all elements in array

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related