I have a question and answer website like SO. Also I have a table which contains both the questions and answers and their edited version. Here is my table structure:
// QandA
+----+---------+---------------------------+---------+------+-----------+
| id | title | body | related | type | edited_id |
+----+---------+---------------------------+---------+------+-----------+
| 1 | title1 | question content | NULL | 0 | NULL |
| 2 | | answer content | 1 | 1 | NULL |
| 3 | title2 | question content | NULL | 0 | NULL |
| 4 | | answer content | 3 | 1 | NULL |
| 5 | | answer content | 1 | 1 | NULL |
| 6 | | answer content (edited) | NULL | 1 | 2 |
| 7 | title3 | question content | NULL | 0 | NULL |
| 8 | title1 | question content (edited) | NULL | 0 | 1 |
| 9 | | answer content | 7 | 1 | NULL |
| 10 | title1 | question content (edited) | NULL | 0 | 1 |
| 11 | title3 | question content (edited) | NULL | 0 | 7 |
+----+---------+---------------------------+---------+------+-----------+
Column explanations:
related
column:
NULL
for both questions and edited version of questions/answers{the id of its own question}
for answers
type
column:
0
for questions1
for answers
edited_id
column: (the id of original post)
NULL
means it is a original question/answer{any number}
means it is a edited version of a question/answer.
Now I need a query to select a question and all its answers. Noted that I need to select the last edited version of them (if they have been edited).
Example1: I have this value: :id = 1
and I want this output:
+----+---------+---------------------------+---------+------+-----------+
| id | title | body | related | type | edited_id |
+----+---------+---------------------------+---------+------+-----------+
| 10 | title1 | question content (edited) | NULL | 0 | 1 |
| 6 | | answer content (edited) | NULL | 1 | 2 |
| 5 | | answer content | 1 | 1 | NULL |
+----+---------+---------------------------+---------+------+-----------+
Example2: I have this value: :id = 3
and I want this output:
+----+---------+---------------------------+---------+------+-----------+
| id | title | body | related | type | edited_id |
+----+---------+---------------------------+---------+------+-----------+
| 3 | title2 | question content | NULL | 0 | NULL |
| 4 | | answer content | 3 | 1 | NULL |
+----+---------+---------------------------+---------+------+-----------+
Example2: I have this value: :id = 7
and I want this output:
// QandA
+----+---------+---------------------------+---------+------+-----------+
| id | title | body | related | type | edited_id |
+----+---------+---------------------------+---------+------+-----------+
| 11 | title3 | question content (edited) | NULL | 0 | 7 |
| 9 | | answer content | 7 | 1 | NULL |
+----+---------+---------------------------+---------+------+-----------+
Here is my current query:
SELECT *
FROM QandA
WHERE (id = :id AND type = 0) OR
(related = :id AND type = 1)
ORDER BY type -- noted that the order of answers doesn't matter
As you see, my query doesn't support edited version. Anyway, how can I replace edited version of posts when there is a edited row of that post?
Note: Please don't tell me "don't keep both the questions and answers in the same table", Because I know it. But now I need to solve problem above.
Best Answer
You need a groupwise max. I think you will need one query to find the "latest" question, then one for the latest answer. Then
UNION ALL
the results together in order to get them in the same resultset.