Martin AJ Martin AJ - 3 months ago 6
MySQL Question

How can select all related posts?

I have this table structure:

// qanda
+----+----------------------------------------+---------+-----------+------+
| Id | body | related | user_id | free |
+----+----------------------------------------+---------+-----------+------+
| 1 | content of question1 | null | 2 | null |
| 2 | content of first answer for question1 | 1 | 2 | null |
| 3 | content of question2 | null | 6 | 300 |
| 4 | content of second answer for question1 | 1 | 4 | null |
| 5 | content of first answer for question2 | 3 | 2 | null |
| 6 | content of question3 | NULL | 8 | null |
| 7 | content of first answer for question3 | 6 | 4 | null |
| 8 | content of second answer for question3 | 6 | 2 | null |
+----+----------------------------------------+---------+-----------+------+

/* related column: it is NULL for questions, and the id of its own question for answers.

free column: Actually that's just for questions. NULL means it is a free question
and any number else means it isn't. (answers always are NULL) */


I need to select all answers of a user that belongs to a free question. For example I want to select all answers of this user:
$user = 2
. So this is expected result:

+----+----------------------------------------+---------+-----------+------+
| Id | body | related | user_id | free |
+----+----------------------------------------+---------+-----------+------+
| 2 | content of first answer for question1 | 1 | 2 | null |
| 8 | content of second answer for question3 | 6 | 2 | null |
+----+----------------------------------------+---------+-----------+------+


How can I do that?

SELECT a.*
FROM qanda q
JOIN qanda a
ON q.id = a.related
WHERE related IS NOT NULL -- this specifics answers
AND user_id = 2
AND ...

Answer
-- Working Query
SELECT 
    answer.*
FROM
    qanda question
        JOIN
    qanda answer ON question.Id = answer.related
WHERE
    answer.related IS NOT NULL
        AND answer.user_id = 2
        AND question.free IS NULL;

I took the liberty of changing the naming a bit during my test to clarify the query a bit.

You were missing AND question.free IS NULL

With the query you had before you would've gotten another record of that user since you didn't check for the condition of the actual related question to be empty.

All in all I would consider another database design but as I know too well that's not always possible.

Working example