SQL Как запросить отношение многие ко многим

Предполагая эти таблицы:

• Сообщения: идентификатор, автор, дата, содержание
• Теги: идентификатор, имя
• PostTags: post_id, tag_id

Последнюю таблицу часто называют таблицей соединения, и она упрощает отношения "многие ко многим" между публикациями и тегами.

SELECT p.*
FROM posts p
JOIN posttags pt ON p.id = pt.post_id
JOIN tags t ON pt.tag_id = t.id
WHERE t.name = 'sql'

По сути, думайте об отношениях «многие ко многим» как о двух отношениях «один ко многим», потому что именно так они реализованы в обычных СУБД. Таким образом, приведенный выше запрос имеет соединение «один ко многим» из записей в посттеги, а другое — из тегов в посттеги.

Созданная мною таблица PostTags имеет составной первичный ключ, равный (post_id, tag_id). Эта комбинация будет уникальной. Многие не одобряют составные ключи, поэтому часто можно увидеть людей, создающих столбец первичного ключа:

• PostTags: id, post_id, tag_id

Любой метод хорош. Это во многом философская разница.

Обновление: если вы хотите выбрать все сообщения с определенным тегом и все теги, которые есть у этих сообщений, то:

SELECT p.*
FROM posts p
JOIN posttags pt ON p.id = pt.post_id
JOIN tags t ON pt.tag_id = t.id
WHERE p.id IN
  (SELECT post_id
  FROM PostTags pt
  JOIN tags t ON pt.tag_id = t.id
  WHERE t.name = 'xyz')

Другой способ сделать это:

SELECT p.*
FROM posts p
JOIN posttags pt ON p.id = pt.post_id
JOIN tags t ON pt.tag_id = t.id
WHERE EXISTS
  (SELECT post_id
  FROM PostTags pt
  JOIN tags t ON pt.tag_id = t.id
  WHERE t.name = 'xyz'
  AND pt.post_id = p.id)

Что работает лучше, нужно будет протестировать, и оно может варьироваться в зависимости от поставщика и версии базы данных. Хороший оптимизатор (например, Oracle), вероятно, оптимизирует их, чтобы они выполнялись одинаково. Другие не могут.

Теперь это вернет вам строки следующим образом:

Post 1, tag 1
Post 1, tag 2
Post 3, tag 2
Post 3, tag 3

поэтому вам нужно будет их объединить, предпочтительно в логике приложения, а не в SQL. В некоторых РСУБД есть расширения для таких вещей, зависящие от поставщика, например MySQL GROUP_CONCAT().