Cristy Cristy - 2 years ago 68
SQL Question

MySQL multiple elemnts match where (A, B, Z) IN (A,B,C,E,Z)

I am trying to create a

query that gets all rows in the database that have ALL the tags given.

At the moment I have this:

WHERE "tag1" IN (SELECT tag FROM tag_list WHERE =

This works fine for one tag but if I have multiple tags I don't want to copy paste this query many times and join them with
because it seems slow to select all tags multiple times.

Is there any way I can do something like this?

WHERE ("tag1", "tag2", "tag3") IN (SELECT tag FROM tag_list WHERE =

Meaning that my client has all the tags: tag1, tag2, tag3.

If I try to execute the last query I get this error:
Operand should contain 3 column(s)

So, my current solution would be something like this:

WHERE "tag1" IN (SELECT tag FROM tag_list WHERE =
AND "tag2" IN (SELECT tag FROM tag_list WHERE =
AND "tag3" IN (SELECT tag FROM tag_list WHERE =

Which doesn't seem optimal at all.

Tables structure:

client [id, name]
tag_list [FK id references, tag]

So each client can have multiple tags, and I want to only retrieve the clients that have ALL the given tags.

sba sba
Answer Source

Omit the subquery and you should be good:

JOIN tag_list tag1 ON = AND tag1.tag = "tag1"
JOIN tag_list tag2 ON = AND tag2.tag = "tag2"
JOIN tag_list tag3 ON = AND tag3.tag = "tag3"

I can't really think of a more efficient way to do this. Mysql should be able to optimize this easily.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download