Intersection tables and queries and MySQL

Question

I'm building a StackOverflow mini-clone. I have the following database relations:

questions 
- question_id
- title

tags
- tag_id
- name

question_tags
- foreign key tag_id
- foreign key question_id

Supposedly, this is the proper way to organize a tagging system.

Now, how could I query all questions with the tag 'java', and at the same time, when listing those questions, also show all the tags that each question is associated with?

I feel like denormalizing and putting an extra field into questions, say tag_string, which would have a listing of tags as CSV, so I can have a simpler query for this case.

Of course, it is not a problem when showing a single question:

  SELECT * FROM questions LEFT OUTER JOIN question_tags USING(question_id)
                          LEFT OUTER JOIN tags USING(tag_id)
                          WHERE question_id = 42;

score 0 · Answer 1 · edited May 23 '17 at 12:27

0

Not entirely sure, but you could consider using CONCAT() in a subquery to combine all of them. May not be very good for performance

-edit-

The accepted answer on Using GROUP_CONCAT on subquery in MySQL may help you.

edited May 23 '17 at 12:27

Community

1
1

answered Nov 26 '11 at 15:58

Tom van der Woerdt

29,532
7
72
105

score 0 · Accepted Answer · answered Nov 29 '11 at 22:41

Ended up with a solution that looks like this:

    result = g.db.query(u" SELECT q.*, u.fullname, u.reputation, t2.tag_id, t2.name as tag_name, a.answer_id, qv.vote_id, qv.value as vote_value \
                       FROM tags t \
                       JOIN question_tags USING(tag_id) \
                       JOIN questions q USING(question_id) \
                       JOIN users u ON(q.author_id = u.user_id) \
                       JOIN question_tags qt2 ON(qt2.question_id = q.question_id) \
                       JOIN tags t2 ON(t2.tag_id = qt2.tag_id) \
                       LEFT OUTER JOIN answers a ON (a.question_id = q.question_id) \
                       LEFT OUTER JOIN question_votes qv ON (qv.question_id = q.question_id) \
                       WHERE (t.name = %s);", name)

Somehow I was missing the fact that it is possible to join twice on the same table - first for matching tags and later for all tags for each question.

Intersection tables and queries and MySQL

2 Answers2