0

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.

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
orlb
  • 1
  • 1

0 Answers0