stack stack - 5 months ago 8
SQL Question

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

Answer

You need CASE here:

SELECT id,
       title,
       content,
       CASE
            WHEN type = 0 THEN      -- get tags only when post type is QUESTION
                (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)
            ELSE        -- else consider it an ANSWER and don't get tags
                NULL
        END tag
FROM QandA
WHERE id = :id2 OR related = :id3
Comments