Выбор sql из таблицы «один ко многим»

Далее используются x, y и z для обозначения идентификаторов тем, поскольку они не были предоставлены для примеров.

Использование СОЕДИНЕНИЙ:

SELECT m.*
  FROM MESSAGES m
  JOIN MESSAGETOPICRELATIONS mtr ON mtr.messageid = m.messageid
  JOIN TOPICS tx ON tx.topicid = mtr.topicid
                AND tx.topicid = x
  JOIN TOPICS ty ON ty.topicid = mtr.topicid
                AND ty.topicid = y
  JOIN TOPICS tz ON tz.topicid = mtr.topicid
                AND tz.topicid = z

Использование GROUP BY/HAVING COUNT(*):

  SELECT m.*
    FROM MESSAGES m
    JOIN MESSAGETOPICRELATIONS mtr ON mtr.messageid = m.messageid
    JOIN TOPICS t ON t.topicid = mtr.topicid
   WHERE t.topicid IN (x, y, z)
GROUP BY m.messageid, m.messagetext
  HAVING COUNT(*) = 3

Из этих двух подход JOIN более безопасен.

GROUP BY/HAVING полагается на то, что MESSAGETOPICRELATIONS.TOPICID либо является частью первичного ключа, либо имеет уникальное ограничение ключа, чтобы гарантировать отсутствие дубликатов. В противном случае у вас может быть более 2 экземпляров одной и той же темы, связанных с сообщением, что будет ложным срабатыванием. Использование HAVING COUNT(DISTINCT ... устраняет любые ложные срабатывания, но поддержка зависит от базы данных — MySQL поддерживает ее в 5.1+, но не в 4.1. Oracle, возможно, придется подождать до понедельника, чтобы протестировать SQL Server...

Я просмотрел комментарий Билла о том, что не нужно присоединение к таблице TOPICS:

SELECT m.*
  FROM MESSAGES m
  JOIN MESSAGETOPICRELATIONS mtr ON mtr.messageid = m.messageid
                                AND mtr.topicid IN (x, y, z)

... вернет ложные срабатывания - строки, которые соответствуют хотя бы одному из значений, определенных в предложении IN. И:

SELECT m.*
  FROM MESSAGES m
  JOIN MESSAGETOPICRELATIONS mtr ON mtr.messageid = m.messageid
                                AND mtr.topicid = x
                                AND mtr.topicid = y
                                AND mtr.topicid = z

... вообще ничего не вернет, потому что topicid никогда не может быть всеми значениями сразу.

Вот глубоко неэлегантное решение

SELECT
     m.MessageID
    ,m.MessageText
FROM
    Messages m
WHERE
    m.MessageID IN (
    SELECT
        mt.MessageID
    FROM
        MessageTopicRelations mt
    WHERE
        TopicID IN (1,4,5)// List of topic IDS
    GROUP BY
        mt.MessageID
    HAVING
        count(*) = 3 //Number of topics
    )

Редактировать: спасибо @Paul Creasey и @OMG Ponies за то, что нашли недостатки в моем подходе.
Правильный способ сделать это — самостоятельно присоединиться к каждой теме; как показано в ведущем ответе.

Еще одна крайне неэлегантная запись:

select m.MessageText
       , t.TopicName
  from Messages m
       inner join MessageTopicRelations mtr
       on mtr.MessageID = m.MessageID
       inner join Topics t
       on t.TopicID = mtr.TopicID
   and
       t.TopicName = 'topic1'

UNION 

select m.MessageText
       , t.TopicName
  from Messages m
       inner join MessageTopicRelations mtr
       on mtr.MessageID = m.MessageID
       inner join Topics t
       on t.TopicID = mtr.TopicID
   and
       t.TopicName = 'topic2'
...

Re: ответ OMG Ponies, вам не нужно присоединяться к таблице TOPICS. И предложение HAVING COUNT(DISTINCT) отлично работает в MySQL 5.1. Я только что проверил это.

Это то, что я имею в виду:

Использование GROUP BY/HAVING COUNT(*):

  SELECT m.*
    FROM MESSAGES m
    JOIN MESSAGETOPICRELATIONS mtr ON mtr.messageid = m.messageid
   WHERE mtr.topicid IN (x, y, z)
GROUP BY m.messageid
  HAVING COUNT(DISTINCT mtr.topicid) = 3

Причина, по которой я предлагаю COUNT(DISTINCT), заключается в том, что если столбцы (messageid,topicid) не имеют уникального ограничения, вы можете получить дубликаты, что приведет к количеству 3 в группе, даже с менее чем тремя различными значениями.