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.
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)
apple -> fruit red food
banana -> fruit yellow food
cheese -> yellow food
firetruck -> vehicle red
If I want to match (red), I should get apple and firetruck. If I want to match (fruit, food) I should get (apple, banana).
How do I write a SQL query do do what I want?
@Jeremy Ruten,
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.
Second, my problem is that I need to get all objects that match all tags. Substituting your OR for an AND like so:
SELECT object WHERE tag = 'fruit' AND tag = 'food';
Yields no results when run.
Given:
tags table (primary key id)
SELECT distinct o.*
from object o join objecttags ot on o.Id = ot.objectid
join tags t on ot.tagid = t.id
where t.Name = 'fruit' or t.name = 'food';
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. This query will yield duplicates usually, because you will get 1 row of each object, per tag.
If you wish to really do an and in this case, you will need a group by, and a having count = <number of ors> in your query for example.
SELECT distinct o.name, count(*) as count
from object o join objecttags ot on o.Id = ot.objectid
join tags t on ot.tagid = t.id
where t.Name = 'fruit' or t.name = 'food'
group by o.name
having count = 2;