mysql - SQL - 如何从其他表中选择信息

Question

我是 sql 新手，正在尝试构建某种测验。我制作了两个表格，架构显示在图片上。

可能我做错了，但不知道如何改正。我想显示一个问题，所有三个可能的答案和好的答案，所以我得到的答案是文本而不是数字。我试过了

select q.questiontext, q.answer1_id, q.answer2_id, q.answer3_id, a.answertext  
from questions q
join answers a on q.goodanswer = a.id

但我在想要与 ID 相关联的文本的地方得到答案 ID

你能告诉我我做错了什么吗？

Answer 1

您需要为要显示的每一列单独加入 answers 表:

SELECT q.questiontext, a1.answertext AS answer1, a2.answertext AS answer2, a3.answertext as answer3, a4.answertext as good_answer
FROM questions AS q
JOIN answers AS a1 ON a1.id = q.answer1_id
JOIN answers AS a2 ON a2.id = q.answer2_id
JOIN answers AS a3 ON a3.id = q.answer3_id
JOIN answers AS a4 ON a4.id = q.goodanswer