SQL many-to-many matching

many-to-many, sql, tagging

Solution

Given:

- object table (primary key id)

- objecttags table (foreign keys objectId, tagid)

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;

Problem

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.

Original source