rootavish rootavish - 5 months ago 7
SQL Question

Fetch results efficiently from MySQL in many-to-many relationship

I have the following tables:

1) Questions

Question id

Question Content

2) Tags

Tagid

TagContent

3) Tags_Questions

TagId

Question_Id

There exists a many-to-many relationship between questions and tags.

Problem at hand:

For multiple questions(say m), we have to show tags(say n tags with each question) associated with it on a single page (similar to interesting questions on stack overflow homepage).

Solutions we thought of:

1) For every question, JOIN all 3 tables and retrieve tags but this would take m queries and result for every query would further take O(n) time.So total time:O(m*n)

2) Fetch results for all required Question id's and then loop through results.

Is there any other efficient way to fetch results for the same?

Answer

You should be able to obtain what you need with GROUP_CONCAT:

SELECT q.*, GROUP_CONCAT(DISTINCT t.content) as all_tags 
FROM tags_question tq LEFT JOIN tags t ON tq.TagId=t.id LEFT JOIN questions q ON tq.Question_Id=q.id 
GROUP BY tq.Question_Id
Comments