Martin AJ Martin AJ - 3 months ago 8
MySQL Question

How can I select a post and all its related posts?

I have a question-and-answer website like SO. I have a table like this:

// qanda
+----+----------+----------------------+---------+------+
| id | title | content | related | type |
+----+----------+----------------------+---------+------+
| 1 | a title | a content | NULL | 0 |
| 2 | | a content | 1 | 1 |
| 3 | a title | a content | NULL | 0 |
| 4 | | a content | 1 | 1 |
| 5 | | a content | 3 | 1 |
+----+----------+----------------------+---------+------+
/* type column: "0" means it is a question, "1" means it is a answer
related column: it contains the id number of its own question
*/


Now I need to select a question and all its own answers based on a
id
number. (that
id
number can be either the id of a question or the id of an answer)
.

Here is my current query:

SELECT * FROM qanda WHERE id = :id OR related = :id


My query works as well only when
:id
is the id of a question. (I mean it doesn't work correctly if
:id
be the id of a answer)
.




Here is expected result:

assuming either :id = 1 or :id = 2 or :id = 4
+----+----------+----------------------+---------+------+
| id | title | content | related | type |
+----+----------+----------------------+---------+------+
| 1 | a title | a content | NULL | 0 |
| 2 | | a content | 1 | 1 |
| 4 | | a content | 1 | 1 |
+----+----------+----------------------+---------+------+


As I mentioned above, I need to select those three rows if
:id = 1
or
:id = 2
or
:id = 4
. How can I do that?

Answer

The following query should work. The query is divided in 4 parts that are unioned together. Description of each query:

  1. Returns question if :id is a question
  2. Returns answers if :id is a question
  3. Returns question if :id is an answer
  4. Returns answers if :id is an answer

Query:

select q.*
  from quanda q
 where q.id = :id
   and q.type = 0
 UNION ALL
select a.*
  from quanda a
 where a.related = :id
 UNION ALL
select q.*
  from quanda a
  join quanda q
    on q.id = a.related
 where a.id = :id
   and a.type = 1 
 UNION ALL
select a2.*
  from quanda a1
  join quanda a2
    on a2.related = a1.related
 where a1.id = :id
   and a1.type = 1