mysql - 我怎样才能加入空？

Question

这是我的表结构:

-- qanda (stands for questions and answers)
+----+---------+-----------------------------------------------+--------------+
| id |  title  |                      content                  |  question_id |
+----+---------+-----------------------------------------------+--------------+
| 1  | title1  | this is a question                            | NULL         |
| 2  | NULL    | this is an answer                             | 1            |
| 3  | NULL    | this is another answer                        | 1            |
| 4  | title2  | this is another question                      | NULL         |
| 5  | NULL    | this is an answer for the second question     | 4            |
| 6  | NULL    | this is another answer for the first question | 1            |
+----+---------+-----------------------------------------------+--------------+

我知道，如果我将问题和答案放在两个不同的表格中会好得多。但现在我只是想了解 JOIN 在这种情况下究竟是如何工作的。

---

我有一个 qanda 表的 ID，我总是想要一个标题。该 id 可能是问题的 id 或答案的 id。我该怎么做？

---

我想要这样的东西:

SELECT t1.title
FROM qanda t1
INNER JOIN qanda t2
ON t1.id = t2.question_id
WHERE t1.id = :id

我的查询不匹配。以下是预期结果的一些示例:

-- :id = 1
+--------+
| title1 |
+--------+

-- :id = 2
+--------+
| title1 |
+--------+

-- :id = 4
+--------+
| title2 |
+--------+

-- :id = 5
+--------+
| title2 |
+--------+

-- :id = 6
+--------+
| title1 |
+--------+

Answer 1

2 个查询的联合

SELECT t1.title
FROM qanda t1
WHERE t1.id = :id and t1.title IS NOT NULL
UNION
SELECT t1.Title
FROM qanda t2
JOIN qanda t1
ON t1.id = t2.question_id
WHERE t2.id = :id

或者

SELECT DISTINCT t1.title
FROM qanda t1
JOIN qanda t2
ON t1.id = t2.question_id
WHERE :id in ( t2.id, t1.id)