rootavish rootavish - 11 months ago 29
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



3) Tags_Questions



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?


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 LEFT JOIN questions q ON 
GROUP BY tq.Question_Id