samedi 18 juin 2016

How to avoid the execution of a sub query for some rows?

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
       (SELECT i_t.tag_name
        FROM tags t
        JOIN interface_tags i_t
        ON t.id = i_t.tag_id
        WHERE i_t.post_id = :id1) tag
FROM QandA
WHERE id = :id2 OR related = :id3

-- Note: :id1, :id2 and :id3 are identical

As you see my query selects both the question (id = :id2) and all its own answers (related = :id3). There is a subquery which gets all tags for the question. But it will be executed per each row. So that subquery will be executed for both the question and answers. There is a lot of waste process, because answers don't have tags.

What's my question? How can I avoid of executing that subquery for answers? I mean I want to execute that subquery just for the question.


EDIT: 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 |            |
+----+--------+----------------------------------------+------------+

Aucun commentaire:

Enregistrer un commentaire