I have the following tables:
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 tq.TagId=t.id LEFT JOIN questions q ON tq.Question_Id=q.id GROUP BY tq.Question_Id