从多对多表中检索分组

Question

This is kind of hard to explain but I'll do what I can.

I have 2 tables with a many-to-many relationship; there is a linking table defining the relationship. These tables are named Question, QuestionTopic and Topic. Topic has the fields TopicID and TopicName.Question has the fields QuestionID and QuestionText.

I want to retrieve a listing of the topics and the number of questions belonging to that topic. However topics could be grouped together and the count of questions unique to that combination should be known. As an example:

Topic(s) | Count

Topic1,Topic2 | 10

Topic1 | 3

Topic2 | 2

The above implies there are 3 questions unique to topic1 and 10 which have the topics Topic1 and Topic2. The remaining 2 questions have topic2. I'm using MySQL and PHP. Thanks.

Answer 1

"Cheating" solution, using GROUP_CONCAT(). This will not show the count of questions that are not related to any topic:

SELECT
      TopicIds
    , COUNT(*) AS QuestionCount
FROM
      ( SELECT
              QuestionId
            , GROUP_CONCAT(TopicId ORDER BY TopicId) AS Topics
        FROM 
              QuestionTopic
        GROUP BY 
              QuestionId
      ) AS grp
GROUP BY
      Topics