samedi 18 juin 2016

How to select all post's tags?

I have this table:

// QandA
+----+--------+----------------------------------------+------+---------+
| id |  title |                  content               | type | related |
+----+--------+----------------------------------------+------+---------+
| 1  | title1 | content of question1                   | 0    | NULL    |
| 2  |        | content of first answer for question1  | 1    | 1       |
| 3  | title2 | content of question2                   | 0    | NULL    |
| 4  | title3 | content of question3                   | 0    | NULL    |
| 5  |        | content of second answer for question1 | 1    | 1       |
| 6  |        | content of first answer for question3  | 1    | 4       |
| 7  | title4 | content of question4                   | 0    | NULL    |
| 8  |        | content of first answer for question2  | 1    | 3       |
+----+--------+----------------------------------------+------+---------+
-- type colum: it is 0 for questions and 1 for answers.
-- related column: it is NULL for questions and {the id of its own question} for answers.

Also I have these two other tables:

// interface_tags
+---------+--------+
| post_id | tag_id |
+---------+--------+
| 1       | 1      |
| 1       | 5      |
| 3       | 4      |
| 4       | 1      |
| 4       | 2      |
| 4       | 5      |
| 7       | 2      |
+---------+--------+

// tags
+----+----------+
| id | tag_name |
+----+----------+
| 1  | PHP      |
| 2  | SQL      |
| 3  | MySQL    |
| 4  | CSS      |
| 5  | Java     |
| 6  | HTML     |
| 7  | JQuery   |
+----+----------+

And here is my query:

SELECT id,
       title,
       content
FROM QandA
WHERE id = :id1 OR related = :id2

-- Note: :id1, :id2 are identical

As you see it selects both the question (id = :id2) and all its own answers (related = :id3).

What's my question? I need to also select all question's tags. Here is expected output:

-- :id = 1

// QandA
+----+--------+----------------------------------------+------------+
| id |  title |                  content               |   tag      |
+----+--------+----------------------------------------+------------+
| 1  | title1 | content of question1                   | PHP,JAVA   |
| 2  |        | content of first answer for question1  |            |
| 5  |        | content of second answer for question1 |            |
+----+--------+----------------------------------------+------------+

How can I do that?

Aucun commentaire:

Enregistrer un commentaire