Блог

В моей базе данных есть отношение изображений и тегов m: n, которое использует перекрестную таблицу для моделирования этого. Таблица imgs содержит гораздо больше информации, чем просто img_id , но это все, что требуется для уникальной идентификации изображения.

Я хочу найти все img_id , которые содержат как tagA, так и tagB (и tagC и так далее, я создам эту строку, чтобы на самом деле не имело значения, будет ли в ней два или десять тегов).

Теперь, где я застрял, конечно, сначала вы соедините imgs с img_tags с tags , добавьте предложение where для тегов;

 SELECT * 
FROM imgs 
INNER JOIN img_tags ON imgs.img_id = img_tags.img_id 
INNER JOIN tags     ON img_tags.tag_id = tags.tag_id
WHERE tag = 'tagA' OR tag = 'tagB';
  

и тогда вы получите строки с идентичной imgs информацией, отличающиеся только tag и tag_id . Теперь я должен иметь возможность подсчитывать их, ориентируясь только на те, которые отображаются в том же количестве, в каком были предоставлены теги ( Count(*) = n ), а затем использовать group by для их агрегирования? Но я не могу в этом разобраться.
В случае, если это может иметь значение, вы могли бы предположить, что поля в img_tags оба являются внешними ключами, ссылающимися на другие таблицы, однако это не так, они никак не связаны.

Вы можете использовать агрегацию следующим образом:

 SELECT i.* 
FROM imgs i JOIN
     img_tags it
     ON i.img_id = it.img_id JOIN
     tags t
     ON it.tag_id = t.tag_id
WHERE tag IN ('tagA', 'tagB')
GROUP BY i.img_id
HAVING COUNT(*) = 2;
  

Агрегирование с помощью i.img_id безопасно — и поддерживается стандартом SQL — при условии, что img_id это первичный ключ в таблице.

Вот подход, использующий коррелированный подзапрос:

 SELECT i.*
FROM imgs i
WHERE (
    SELECT COUNT(*)
    FROM img_tags it
    INNER JOIN tags t ON it.tag_id = t.tag_id
    WHERE i.img_id = it.img_id AND t.tag IN('tagA', 'tagB')
) = 2
  

Это предполагает отсутствие повторяющихся тегов в вашей структуре данных. В противном случае вы можете использовать COUNT(DISTINCT t.tag) вместо COUNT(*) .

Вы также можете использовать агрегирование:

 SELECT i.id
FROM imgs i
INNER JOIN img_tags it ON i.img_id = it.img_id 
INNER JOIN tags t      ON it.tag_id = t.tag_id
WHERE t.tag IN('tagA', 'tagB')
GROUP BY i.id
HAVING COUNT(*) = 2
  

Если задействовано не так много тегов, я бы просто использовал exists (или not exists, если вы хотите исключить некоторые теги) для этого

 select *
from imgs
where
    exists(select 1 from img_tags it where it.tag_id=(select tag_id from tags where tag='tagA') and it.img_id=imgs.img_id)
    and exists(select 1 from img_tags it where it.tag_id=(select tag_id from tags where tag='tagB') and it.img_id=imgs.img_id);
  

особенно, если вы в конечном итоге захотите выполнить более сложные логические выражения, например (A и (B или не C)).