Tag Database Schema

Question

I have three tables for tagging. The first one is the question table that has a list of question with certain ID. The second one is the tag table that has a list of tag name with certain ID. And the third one is the question_tag table, a collection of question to a tag. A question that has multiple tag means multiple rows in question_tag, I thought of storing an serialized array into the question_tag table but it's in general not a good idea to store array inside of a SQL database.

Below is the schema. Arrow denoting foreign key.

                       -------------------
-----------------      | question_tag    |      
| question      |      -------------------        ------------------
-----------------      | question_tag_ID |        | tag            |
| question_ID   | ---> | question_ID     |        ------------------
-----------------      | tag_ID          | <----- |  tag_ID        |
                       -------------------        |  tag_name      |
                                                  ------------------

I want to make a query that will output this table below.

----------------------------------------------------
| question_id | tag_name                           |
----------------------------------------------------
| 1           | algebra, calculus, differentiation |
| 2           | calculus                           |
| 3           | algebra, trigonometry              |
----------------------------------------------------

How do I manage to do this query? I thought about SELECTING from question and JOINING a temporary table of SELECT tag.tag_name FROM tag WHERE question_tag.tag_ID = tag.tag_ID, but how do I output this RIGHT column (tag_name) like the table above.

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.

This is what I have come up with:

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

Hey neovee, We'd need to know what database system you're using to help you with the query. I have the same schema for an app I've created, but it's in SQL server. Please let us know. — JSideris, Mar 14 '14 at 20:57
sorry I forgot to mention I used MySQL, does the code differs a lot from the SQL server? Or at least does the concept that you use apply anywhere? Maybe I can manage to solve it in MySQL — neovee, Mar 14 '14 at 21:11
See my update below. This is strictly going off of other reference material and has not been tested. — JSideris, Mar 14 '14 at 22:12

score 1 · Accepted Answer · edited May 23 '17 at 11:49

1

You'll need to aggregate and concatenate. Please check out some of the related questions:

Does T-SQL have an aggregate function to concatenate strings?

Implode type function in SQL Server 2000?

Concatenate row values T-SQL

How to use GROUP BY to concatenate strings in MySQL?

Aggregate String Concatenation in Oracle 10g

Create a delimitted string from a query in DB2

How to concatenate strings of a string field in a PostgreSQL 'group by' query?

UPDATE

While I don't have a mysql DB to test this on, going off of one of the above links (with the knowledge this is mysql), I've designed the following query:

SELECT 
    qt.question_id, 
    GROUP_CONCAT(t.tag_name SEPARATOR ',') 
FROM question_tag qt
LEFT JOIN tag t ON t.tag_id = qt.tag_id
GROUP BY qt.question_id;

Please try this out and let me know if it works.

edited May 23 '17 at 11:49

Community

1
1

answered Mar 14 '14 at 20:41

JSideris

5,101
3
32
50

How do you now neovee uses SQL Server? – Mar 14 '14 at 20:43
@a_horse_with_no_name Probably doesn't. My real answer is not the links it's the process that can be looked up. I'll update my answer when more info is provided. – JSideris Mar 14 '14 at 20:48
@Bizorke I managed to do it like the way you suggested. The GROUP_CONCAT function is exactly what I needed – neovee Mar 15 '14 at 11:55

Tag Database Schema

1 Answers1