Below are some simplified table structures
employee
+----+----------+
| id | name |
+----+----------+
| 1 | "Andrew" |
| 2 | "April" |
| 3 | "John" |
+----+----------+
tag
+----+---------+
| id | name |
+----+---------+
| 1 | "Tag 1" |
| 2 | "Tag 2" |
| 3 | "Tag 3" |
| 4 | "Tag 4" |
+----+---------+
employee_tag
+------------------+--------+
| id | employee_id | tag_id |
+------------------+--------+
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 1 | 3 |
| 4 | 1 | 4 |
| 5 | 2 | 1 |
| 6 | 2 | 4 |
| 7 | 3 | 4 |
+------------------+--------+
A simple data structure in where you can see that:
- Andrew has the tags : Tag 1, Tag 2, Tag 3 and Tag 4
- April has the tags: Tag 1 and Tag 4
- John has the tags: Tag 4
I want to have a list of all employees having all the tags I specify by name. So, suppose I want to have the employees which are tagged with "Tag 1" and "Tag 4" I wrote this query.
SELECT employee.id, employee.name FROM employee
INNER JOIN employee_tag employee_tag_alias_1 ON employee_tag_alias_1.employee_id = employee.id
INNER JOIN tag tag_alias_1 ON employee_tag_alias_1.tag_id = tag_alias_1.id
INNER JOIN employee_tag employee_tag_alias_2 ON employee_tag_alias_2.employee_id = employee.id
INNER JOIN tag tag_alias_2 ON employee_tag_alias_2.tag_id = tag_alias_2.id
WHERE tag_alias_1.name = "Tag 1" AND tag_alias_2.name = "Tag 4"
As you can see I have multiple inner joins for each tag I'm searching for using aliases.
The results in this case are as expected. It returns Andrew and April since they are the ones having these tags.
Specifying another tag to search for, I create two other joins with a unique alias like this
INNER JOIN employee_tag employee_tag_alias_3 ON employee_tag_alias_3.employee_id = employee.id
INNER JOIN tag tag_alias_3 ON employee_tag_alias_3.tag_id = tag_alias_3.id
The tables in this example are small, but in reality I have dozens of tags. So if I would search for 100 different tags I have to create 200 different joins and then MySQL throws the error : Too many tables; MySQL can only use 61 tables in a join.
I was using the IN operator first like this :
SELECT employee.id, employee.name FROM employee
INNER JOIN employee_tag ON employee_tag.employee_id = employee.id
INNER JOIN tag ON employee_tag.tag_id = tag.id
WHERE tag.name IN ("Tag 1", "Tag 4")
GROUP BY employee.id
In this case the result was unwanted (though expected), because John is in the list as well.
So the question is: how can I accomplish to get only those employees having all the tags (even 100) specified.
The database engine is MySql and I generate the statement through PHP. Suggesting that I should use any other database or structure is not an option because I'm not the one to decide this.