SQL many-to-many соответствие

Дано:

• таблица объектов (идентификатор первичного ключа)
• таблица objecttags (внешние ключи objectId, tagid)

• таблица тегов (идентификатор первичного ключа)

  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';
  

Это кажется обратным, так как вы хотите и, но проблема в том, что 2 тега не находятся в одной строке, и поэтому, и ничего не дает, так как 1 одиночный ряд не может быть одновременно фруктом и едой. Этот запрос обычно дает дубликаты, потому что вы получите по 1 строке каждого объекта на тег.

Если вы действительно хотите сделать и в этом случае, вам понадобится group by и having count = <number of ors> в вашем запросе, например.

  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;

О боже, возможно, я неправильно истолковал ваш первоначальный комментарий.

Самый простой способ сделать это в SQL - это иметь три таблицы:

1) Tags ( tag_id, name )
2) Objects (whatever that is)
3) Object_Tag( tag_id, object_id )

Затем вы можете задать практически любой вопрос, который вы хотите получить из данных быстро, легко и эффективно (при условии, что вы индексируете соответствующим образом). Если вы хотите стать модным, вы также можете разрешить многозначные теги (есть элегантный способ, и я могу придумать менее элегантный способ).

Я предполагаю, что это то, что у вас есть, так что этот SQL ниже будет работать:

Буквальный путь:

    SELECT obj 
      FROM object
     WHERE EXISTS( SELECT * 
                     FROM tags 
                    WHERE tag = 'fruit' 
                      AND oid = object_id ) 
       AND EXISTS( SELECT * 
                     FROM tags 
                    WHERE tag = 'Apple'
                      AND oid = object_id )

Есть и другие способы, которыми вы можете это сделать, например:

SELECT oid
  FROM tags
 WHERE tag = 'Apple'
INTERSECT
SELECT oid
  FROM tags
 WHERE tag = 'Fruit'

@Kyle: ваш запрос должен быть более похожим:

SELECT object WHERE tag IN ('fruit', 'food');

Ваш запрос искал строки, в которых тег был одновременно fruit AND food, что невозможно, поскольку поле может иметь только одно значение, а не оба одновременно.

Объедините предложение Стива м. с предложением Джереми вы получите одну запись с тем что вы ищете:

select object
from tblTags
where tag = @firstMatch
and (
       @secondMatch is null 
       or 
       (object in (select object from tblTags where tag = @secondMatch)
     )

Теперь это не очень хорошо масштабируется, но он получит то, что вы ищете. Я думаю, что есть лучший способ сделать это, чтобы вы могли легко иметь N совпадающих элементов без большого влияния на код, но в настоящее время он ускользает от меня.

Я рекомендую следующую схему.

Objects: objectID, objectName
Tags: tagID, tagName
ObjectTag: objectID,tagID

Со следующим запросом.

select distinct
    objectName
from
    ObjectTab ot
    join object o
        on o.objectID = ot.objectID
    join tabs t
        on t.tagID = ot.tagID
where
    tagName in ('red','fruit')

Я бы предложил сделать так, чтобы ваша таблица имела 1 тег на запись, как это:

 apple -> fruit
 apple -> red
 apple -> food
 banana -> fruit
 banana -> yellow
 banana -> food

Тогда вы могли бы просто

 SELECT object WHERE tag = 'fruit' OR tag = 'food';

Если вы действительно хотите сделать это по-своему, Вы можете сделать это так:

 SELECT object WHERE tag LIKE 'red' OR tag LIKE '% red' OR tag LIKE 'red %' OR tag LIKE '% red %';