title: Tag Database Schema
body: <p>I have three tables for tagging. The first one is the <code>question</code> table that has a list of question with certain ID. The second one is the <code>tag</code> table that has a list of tag name with certain ID. And the third one is the <code>question_tag</code> table, a collection of question to a tag. A question that has multiple tag means multiple rows in <code>question_tag</code>, I thought of storing an serialized array into the <code>question_tag</code> table but it's in general not a good idea to store array inside of a SQL database.</p>
  
  <p>Below is the schema. Arrow denoting foreign key.</p>
  
  <pre><code>                       -------------------
  -----------------      | question_tag    |      
  | question      |      -------------------        ------------------
  -----------------      | question_tag_ID |        | tag            |
  | question_ID   | ---&gt; | question_ID     |        ------------------
  -----------------      | tag_ID          | &lt;----- |  tag_ID        |
                         -------------------        |  tag_name      |
                                                    ------------------
  </code></pre>
  
  <p>I want to make a query that will output this table below.</p>
  
  <pre><code>----------------------------------------------------
  | question_id | tag_name                           |
  ----------------------------------------------------
  | 1           | algebra, calculus, differentiation |
  | 2           | calculus                           |
  | 3           | algebra, trigonometry              |
  ----------------------------------------------------
  </code></pre>
  
  <p>How do I manage to do this query? I thought about SELECTING from <code>question</code> and <code>JOINING</code> a temporary table of <code>SELECT tag.tag_name FROM tag WHERE question_tag.tag_ID = tag.tag_ID</code>, but how do I output this RIGHT column (tag_name) like the table above.</p>
  
  <p>I would really appreciate it if you can help me with this SQL query, I am guessing that I need to do a nesting SELECT query for the RIGHT (tag_name) column, then JOIN it to the question_table. But I am not sure how to the nesting of SELECT query.</p>
  
  <p>This is what I have come up with:</p>
  
  <p><code>SELECT * FROM question as Q LEFT JOIN (SELECT T.tag_name FROM tag as T WHERE T.tag_id IN (SELECT QT.tag_id FROM question_tag AS QT WHERE QT.question_ID = Q.id)) AS QT_T</code></p>