stack stack - 6 months ago 6
SQL Question

How to get the value of related rows?

I have a table like this:

// QandA
+----+----------+----------------+------+---------+-----------+
| id | subject | acceptedanswer | type | related | id_author |
+----+----------+----------------+------+---------+-----------+
| 1 | subject1 | NULL | 0 | NULL | 123 |
| 2 | | 1 | 1 | 1 | 452 |
| 3 | subject2 | NULL | 0 | NULL | 635 |
| 4 | | 1 | 1 | 3 | 432 |
| 5 | | NULL | 1 | 1 | 246 |
+----+----------+----------------+------+---------+-----------+

/* columns explanations:
- acceptedanswer can be NULL, 0, 1. (0 and NULL are identical). (it is NULL for questions)
- type is 0 for questions and 1 for answers.
- related is NULL for questions. For answers is containing the number of its own question
*/


Well I have two parameters:


  • id
    number of an answer:
    $id = 5

  • id_author
    of that answer
    $id_author = 246



Now I'm trying to get these two things:


  • the subject of its owe question

  • specifying has its own question any accepted-answer or not.



Here is my query:

SELECT t2.subject
FROM qanda t1
JOIN qanda t2 ON t1.related = t2.id AND t2.type = 0
WHERE t1.id = $id AND t2.id_author = $id_author


Current result:

+----------+
| subject1 |
+----------+


Expected result:

+----------+---+
| subject1 | 1 |
+----------+---+
-- ^ this 1 means the question of this answer has a accepted answer
-- ^ this should be 0 if there isn't any accepted answer for that question


How can I do that?

Answer

I suspect that you're after something like this...

SELECT -- or SELECT DISTINCT
       x.subject
     , y.id IS NOT NULL has_accepted 
  FROM qanda x 
  LEFT 
  JOIN qanda y 
    ON y.related = x.id 
   AND y.type = 1 
   AND y.acceptedanswer = 1 
 WHERE x.type = 0 -- or WHERE x.related IS NULL;

Note that both subjects have accepted answers.