Cristy Cristy - 11 months ago 33
SQL Question

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

I am trying to create a

MySQL
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 client.id = tag_list.id)


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
AND
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 client.id = tag_list.id)


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 client.id = tag_list.id)
AND "tag2" IN (SELECT tag FROM tag_list WHERE client.id = tag_list.id)
AND "tag3" IN (SELECT tag FROM tag_list WHERE client.id = tag_list.id)


Which doesn't seem optimal at all.

Tables structure:

client [id, name]
tag_list [FK id references client.id, 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

Omit the subquery and you should be good:

JOIN tag_list tag1 ON client.id = tag1.id AND tag1.tag = "tag1"
JOIN tag_list tag2 ON client.id = tag2.id AND tag2.tag = "tag2"
JOIN tag_list tag3 ON client.id = tag3.id 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.