Getting groups from many-to-many tables

Question

It is difficult to explain, but I will do everything in my power.

I have 2 tables with a many-to-many relationship; there is a link table that defines the relationship. These tables are called Question, QuestionTopic, and Topic. The topic has the fields TopicID and TopicName.Question has the fields QuestionID and QuestionText.

I want to get a list of topics and the number of questions related to this topic. However, topics can be grouped together, and the number of questions unique to this combination should be known. As an example:

Theme | Count

Topic1, Topic2 | ten

Theme1 | 3

Theme2 | 2

It follows from the above that there are 3 questions unique to topic1 and 10, which have topics Topic1 and Topic2. The remaining 2 questions have topic2. I use MySQL and PHP. Thanks.

Answer 1

Fool a solution using GROUP_CONCAT() . This will not show the number 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 

Answer 2

did you try GROUP_CONCAT?

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

then u can group by topic id.

Answer 3

GROUP_CONCAT as above will work. But on the other hand, since I see that your database structure is not suitable for your task. It seems like it is too normalized, and you need to do some de-normalization and migration.

Since you have theme groups, I suggest you create two more tables: 1) Group Topic: Group | A topic is a list of all the unique combinations ("groups") of topics in questions. 2) GroupQuestions: Group | The question is to associate a question with a group of topics that it covers.

Then the solution for your task will be a simple group on demand in GroupQuestions.