Mysql - join the same table's data
I have a table where all comments are stored. Some comments are questions, other - answers. So, my table looks like this:
id parent_id message is_answer answered
1 0 2x2 0 1
2 1 4 1 0
3 0 5x9 0 0
4 1 2x9 0 0
5 1 2x8 0 1
6 5 16 1 0
And as a result I'd like to get this table:
q_id q_message q_answer
1 2x2 4
3 5x9 开发者_StackOverflow社区 NULL
4 2x9 NULL
5 2x8 16
So, I'd like to get all questions and answers to them, if they exist.
There is nothing that prevents you from joining a table to itself.
SELECT q.id AS q_id, q.message AS q_message, a.message AS q_answer
FROM table AS q
LEFT JOIN table AS a ON a.parent_id = q.id
WHERE q.is_answer = 0;
Note that this does not quite give the output you said you want... because it shows all answers to the question:
+------+-----------+----------+
| q_id | q_message | q_answer |
+------+-----------+----------+
| 1 | 2x2 | 4 |
| 1 | 2x2 | 2x9 |
| 1 | 2x2 | 2x8 |
| 3 | 5x9 | NULL |
| 4 | 2x9 | NULL |
| 5 | 2x8 | 16 |
+------+-----------+----------+
6 rows in set (0.00 sec)
Your requested output only shows the answer "4" for question 1. I'm not sure if that was intentional on your part--if so, you'll need some additional GROUP BY, or other logic, to filter out the answers you do not want displayed.
not tested, but this should do the trick:
select q.id as q_id,q.message as q_message ,a.message as q_answer FROM comments q LEFT OUTER JOIN comments a ON (a.parent_id = q.id and a.is_answer=1 and q.is_answer=0)
I think your table design could do without the is_answer column, you could just set parent_id = NULL if its a question?
精彩评论