<p>I'm implementing a tagging system for a website. There are multiple tags per object and multiple objects per tag. This is accomplished by maintaining a table with two values per record, one for the ids of the object and the tag.</p>

<p>I'm looking to write a query to find the objects that match a given set of tags. Suppose I had the following data (in [object] -> [tags]* format)</p>

<pre><code>apple -&gt; fruit red food
banana -&gt; fruit yellow food
cheese -&gt; yellow food
firetruck -&gt; vehicle red
</code></pre>

<p>If I want to match (red), I should get apple and firetruck. If I want to match (fruit, food) I should get (apple, banana).</p>

<p>How do I write a SQL query do do what I want?</p>

<p>@Jeremy Ruten,</p>

<p>Thanks for your answer. The notation used was used to give some sample data - my database does have a table with 1 object id and 1 tag per record.</p>

<p>Second, my problem is that I need to get all objects that match all tags. Substituting your OR for an AND like so:</p>

<pre><code>SELECT object WHERE tag = 'fruit' AND tag = 'food';
</code></pre>

<p>Yields no results when run.</p>

[{'answer_id': 24720, 'author': 'Paige Ruten', 'author_id': 813, 'author_profile': 'https://Stackoverflow.com/users/813', 'pm_score': -1, 'selected': False, 'text': "<p>I'd suggest making your table have 1 tag per record, like this:</p>\n\n<pre><code> apple -&gt; fruit\n apple -&gt; red\n apple -&gt; food\n banana -&gt; fruit\n banana -&gt; yellow\n banana -&gt; food\n</code></pre>\n\n<p>Then you could just</p>\n\n<pre><code> SELECT object WHERE tag = 'fruit' OR tag = 'food';\n</code></pre>\n\n<p>If you really want to do it your way though, you could do it like this:</p>\n\n<pre><code> SELECT object WHERE tag LIKE 'red' OR tag LIKE '% red' OR tag LIKE 'red %' OR tag LIKE '% red %';\n</code></pre>\n"}, {'answer_id': 24745, 'author': 'Steve M', 'author_id': 1693, 'author_profile': 'https://Stackoverflow.com/users/1693', 'pm_score': 0, 'selected': False, 'text': "<p>@Kyle: Your query should be more like:</p>\n\n<pre>\nSELECT object WHERE tag IN ('fruit', 'food');\n</pre>\n\n<p>Your query was looking for rows where the tag was both fruit AND food, which is impossible seeing as the field can only have one value, not both at the same time.</p>\n"}, {'answer_id': 24754, 'author': 'DevelopingChris', 'author_id': 1220, 'author_profile': 'https://Stackoverflow.com/users/1220', 'pm_score': 3, 'selected': True, 'text': "<p>Given:</p>\n\n<ul>\n<li>object table (primary key id)</li>\n<li>objecttags table (foreign keys objectId, tagid)</li>\n<li><p>tags table (primary key id)</p>\n\n<pre><code>SELECT distinct o.*\n  from object o join objecttags ot on o.Id = ot.objectid\n                join tags t on ot.tagid = t.id\n where t.Name = 'fruit' or t.name = 'food';\n</code></pre></li>\n</ul>\n\n<p>This seems backwards, since you want and, but the issue is, 2 tags aren't on the same row, and therefore, an and yields nothing, since 1 single row cannot be both a fruit and a food.\nThis query will yield duplicates usually, because you will get 1 row of each object, per tag.</p>\n\n<p>If you wish to really do an and in this case, you will need a <code>group by</code>, and a <code>having count = &lt;number of ors&gt;</code> in your query for example.</p>\n\n<pre><code>  SELECT distinct o.name, count(*) as count\n    from object o join objecttags ot on o.Id = ot.objectid\n                  join tags t on ot.tagid = t.id\n   where t.Name = 'fruit' or t.name = 'food'\ngroup by o.name\n  having count = 2;\n</code></pre>\n"}, {'answer_id': 24758, 'author': 'Matt Rogish', 'author_id': 2590, 'author_profile': 'https://Stackoverflow.com/users/2590', 'pm_score': 2, 'selected': False, 'text': "<p>Oh gosh I may have mis-interpreted your original comment. </p>\n\n<p>The easiest way to do this in SQL would be to have three tables:</p>\n\n<pre><code>1) Tags ( tag_id, name )\n2) Objects (whatever that is)\n3) Object_Tag( tag_id, object_id )\n</code></pre>\n\n<p>Then you can ask virtually any question you want of the data quickly, easily, and efficiently (provided you index appropriately). If you want to get fancy, you can allow multi-word tags, too (there's an elegant way, and a less elegant way, I can think of). </p>\n\n<p>I assume that's what you've got, so this SQL below will work:</p>\n\n<p>The literal way: </p>\n\n<pre><code>    SELECT obj \n      FROM object\n     WHERE EXISTS( SELECT * \n                     FROM tags \n                    WHERE tag = 'fruit' \n                      AND oid = object_id ) \n       AND EXISTS( SELECT * \n                     FROM tags \n                    WHERE tag = 'Apple'\n                      AND oid = object_id )\n</code></pre>\n\n<p>There are also other ways you can do it, such as:</p>\n\n<pre><code>SELECT oid\n  FROM tags\n WHERE tag = 'Apple'\nINTERSECT\nSELECT oid\n  FROM tags\n WHERE tag = 'Fruit'\n</code></pre>\n"}, {'answer_id': 24759, 'author': 'Rob Allen', 'author_id': 149, 'author_profile': 'https://Stackoverflow.com/users/149', 'pm_score': 0, 'selected': False, 'text': "<p>Combine Steve M.'s suggestion with Jeremy's you'll get a single record with what you are looking for:</p>\n\n<pre><code>select object\nfrom tblTags\nwhere tag = @firstMatch\nand (\n       @secondMatch is null \n       or \n       (object in (select object from tblTags where tag = @secondMatch)\n     )\n</code></pre>\n\n<p>Now, that doesn't scale very well but it will get what you are looking for. I think there is a better way to go about doing this so you can easily have N number of matching items without a great deal of impact to the code but it currently escapes me.</p>\n"}, {'answer_id': 24761, 'author': 'jason saldo', 'author_id': 1293, 'author_profile': 'https://Stackoverflow.com/users/1293', 'pm_score': 0, 'selected': False, 'text': "<p>I recommend the following schema.</p>\n\n<pre><code>Objects: objectID, objectName\nTags: tagID, tagName\nObjectTag: objectID,tagID\n</code></pre>\n\n<p>With the following query.</p>\n\n<pre><code>select distinct\n    objectName\nfrom\n    ObjectTab ot\n    join object o\n        on o.objectID = ot.objectID\n    join tabs t\n        on t.tagID = ot.tagID\nwhere\n    tagName in ('red','fruit')\n</code></pre>\n"}]
['https://Stackoverflow.com/questions/24715', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/658/']