Left join for three tables

Question

I have 3 tables as follows

TABLE A - User Answerd

 user id    |   question id |   answer option id    |
   1        |           1   |               2       |
   1        |           2   |               1       |

Table B - Question

question id | question text     |
1           | Question 1        |
2           | Question 2        |

TABLE C - ANSWER

answer id   |   question id |   answer option id    |answer text                |
1           |   1           |                   1   |Question 1 answer 1        |
2           |   1           |                   2   |Question 1  answer 2       |
2           |   2           |                   1   |Question 2 answer 1        |
3           |   2           |                   2   |Question 2  answer 2       |

I want to find out which user gave which answer The result should look like this

    1       |   Question 1  |   Question 1 answer 2     |
    1       |   Question 2  |   Question 2 answer 1     |

I tried it so

SELECT * FROM A
LEFT JOIN B ON A.question_id = B.question_id
LEFT JOIN C ON A.answer_option_id = C.answer_option_id

Can anyone here help me?

Answer 1

You need to add group by questionid else it will return 4 rows for each in answer table.

select a.userid, b.Qtext, c.Atext from answered a 
join quest b on a.qid=b.qid 
left join answer c on a.optid=c.optid and a.qid=b.qid  group by a.qid;

Output:

+--------+------------+---------------------+
| userid | Qtext      | Atext               |
+--------+------------+---------------------+
|      1 | question 1 | question 1 answer 2 |
|      1 | question 2 | question 1 answer 1 |
+--------+------------+---------------------+

Answer 2

You are missing ensuring the last join gets the answers for the right question.

Try this:

SELECT * FROM A 
LEFT JOIN B ON A.question_id = B.question_id
LEFT JOIN C ON A.answer_option_id = C.answer_option_id AND A.question_id = C.question_id