Sql selection from one-to-many table

Question

I have 3 tables with these columns below:

Topics:
[TopicID] [TopicName]

Messages:
[MessageID] [MessageText]

MessageTopicRelations
[EntryID] [MessageID] [TopicID]

posts can be more than one topic. Question: ask a couple of topics, I need to get messages that relate to all these topics, and even more so, but they can be about some other topic. A message that relates to Some of these topics data will not be included. I hope I explained my request well. otherwise, I can provide sample data. thank

Answer 1

x, y z , .

JOINs:

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/HOWING 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 .

Answer 2

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
    )

Answer 3

: @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'
...

Answer 4

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

The reason I suggest <T23> is that if the columns are (messageid,topicid)not the only restriction, you can get duplicates that would lead to a count of 3 in the group, even with less than three different values.