I have three tables -
Objects
+-----------+---------------+
| object_id | object_name |
+-----------+---------------+
| 1 | admin_object |
| 2 | hr_object |
| 3 | user_object |
+-----------+---------------+
Tags
+--------+----------+
| tag_id | tag_name |
+--------+----------+
| 1 | admin |
| 2 | hr |
| 3 | user |
+--------+----------+
ObjectTagsNorm
+-----------+--------+
| object_id | tag_id |
+-----------+--------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 2 |
| 2 | 3 |
| 3 | 3 |
+-----------+--------+
such that every object has a unique set of tags. i.e.
admin_object -> (admin, hr, user)
hr_object -> (hr, user)
user_object -> (user)
I would like to query for objects whose tags are all found in a given list, example :
SELECT object_id, GROUP_CONCAT(Tags.tag_name)
FROM Objects
JOIN ObjectTagsNorm ON Objects.object_id = ObjectTagsNorm.object_id
JOIN Tags ON Tags.tag_id = ObjectTagsNorm.tag_id
WHERE
ALL( object_tags IN ('admin', 'hr', 'user') )
I've gotten as far as GROUP_CONCAT(). I can't find solutions for obtaining a variable length list from GROUP_CONCAT() to test against. I'm not sure how I should evaluate each element once I do have the list, I've put 'ALL()' as a placeholder. Any help is appreciated.