Ramiz Raja Ramiz Raja - 4 years ago 81
SQL Question

SQL search for all the projects which have all mentioned tags assigned

I have a SQL query for searching projects based on tags. It's working fine if any of the tag is matching.

SELECT *
FROM projects
WHERE projects.id IN (SELECT taggable_id
FROM taggings
WHERE taggable_type='Project'
AND taggable_id=projects.id
AND taggings.tag_id IN (1, 2, 3))


There are three tables,
Taggings
with columns
id, taggable_id, taggable_type, tag_id
;
Tags
with columns
id, name
; and
Projects
with columns
id, name, description
.

What I want is, to search for all the projects for which all 3 tags are assigned.

Thanks.

Answer Source

You can do this by counting the number of matches. Your subquery is redundant in the comparison to p.id (done both by the IN and by the correlation clause). Here is one method:

SELECT p.*
from projects p
WHERE 3 = (SELECT COUNT(*)
           FROM taggings t
           WHERE t.taggable_type = 'Project' AND
                 t.taggable_id = p.id AND
                 t.tag_id IN (1, 2, 3)
          );

You can also do this using IN:

SELECT p.*
from projects p
WHERE p.id = (SELECT t.taggable_id
              FROM taggings t
              WHERE t.taggable_type = 'Project' AND
                    t.tag_id IN (1, 2, 3)
              GROUP BY t.taggable_id
              HAVING COUNT(*) = 3
             );
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download