mysql - Sql join 查询并多选择一列

Question

我有三个表:answers、questions 和 users，我已将它们全部加入以获得我想要的结果

SELECT answers.answer_content as answer_content,
       answers.id as answer_id,
       answers.created_at as created_at,
       questions.id as question_id, 
       questions.question_title as question_title, 
       questions.question_slug as question_slug, 
       users.id as user_id, 
       users.name as user_name, 
       users.user_slug as user_slug,
                                  FROM answers
                                          JOIN questions
                                               on questions.id = answers.question_id
                                          JOIN users
                                               on users.id = answers.user_id
                                          WHERE questions.question_active = 1 and 
                                                answers.answer_active = 1

现在我又多了一个名为 upvote_answers 的表。该表主要存储用户对答案的赞成票、反对票。

如果用户对某个答案投了赞成票，即列为 user_id、answer_id、upvote(1 或 0 - 分别表示赞成或反对)

所以我的问题是:我想再获得一列带有 bool 结果的列，该结果应该显示当前登录用户的每个答案的赞成票或反对票，或者不显示任何内容

我尝试像这样编写查询

SELECT answers.answer_content as answer_content,
       answers.id as answer_id,
       answers.created_at as created_at,
       questions.id as question_id, 
       questions.question_title as question_title, 
       questions.question_slug as question_slug, 
       users.id as user_id, 
       users.name as user_name, 
       users.user_slug as user_slug,
       (SELECT count(upvote_answers.answer_id) from upvote_answers, answers WHERE upvote_answers.user_id = 2 and answers.id = upvote_answers.answer_id) as upvotedOrNot
                                  FROM answers
                                          JOIN questions
                                               on questions.id = answers.question_id
                                          JOIN users
                                               on users.id = answers.user_id
                                          WHERE questions.question_active = 1 and 
                                                answers.answer_active = 1

当用户登录时，他应该能够看到他是否对某个答案投了赞成票或反对票。

Answer 1

考虑到给定用户只能对任何答案投票一次，这应该可以解决问题:

SELECT answers.answer_content as answer_content,
       answers.id as answer_id,
       answers.created_at as created_at,
       questions.id as question_id, 
       questions.question_title as question_title, 
       questions.question_slug as question_slug, 
       users.id as user_id, 
       users.name as user_name, 
       users.user_slug as user_slug,
       upvote_answers.upvote as upvote
FROM 
    answers
    JOIN questions on questions.id = answers.question_id
    JOIN users on users.id = answers.user_id
    LEFT JOIN upvote_answers ON
        upvote_answers.answer_id = answers.id AND
        upvote_answers.user_id = answers.user_id
WHERE 
    questions.question_active = 1 and 
    answers.answer_active = 1

如果匹配，LEFT JOIN 子句将产生结果，但如果不匹配，则会带来 NULL 值(针对其表)。