CODEWITHSUNDEEP
mysqlsqljoin
stack
stack

Reputation: 10228

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:

Now I'm trying to get these two things:

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?

Upvotes: 0

Views: 62

Answers (2)

Strawberry
Strawberry

Reputation: 33945

If you want to know if the question answered by answer id = 5 has an accepted answer, and assuming (as mentioned in comments) that each question can have only one accepted answer, then something like this (note: two joins) should work... 

SELECT 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
  JOIN qanda z
    ON z.related = x.id
 WHERE x.type = 0 
   AND z.id = 5;

Upvotes: 1

michaJlS
michaJlS

Reputation: 2500

Try this:

SELECT t2.subject, max(COALESCE(t3.accepted, 0))
FROM qanda t1
INNER JOIN qanda t2 ON t1.related = t2.id AND t2.type = 0
INNER JOIN qanda t3 ON t1.related = t3.related
WHERE t1.id = $id AND t2.id_author = $id_author
GROUP BY t3.related

although, I am not sure if I understand your intentions. COALESCE to get rid of nulls, and max() to see if any of answers has been accepted. I am assuming that only answers have not-null values in related column.

Additionally, maybe it would be a better idea to not keep different things in the same table?

Upvotes: 1

Related Questions