如果不回答问题，请回答

Question

基本上，有三个表具有以下结构

第一表: tournament_questions，有所有问题

id tournament_id    question     active   created_at          updated_at
 1         5       Text question    1   2018-12-08 20:28:49     NULL

第二个表: tournament_options，拥有所有选项

id  question_id option  correct active  created_at           updated_at
1      1         1       1       1     2018-12-08 20:29:02     NULL
2      1        26       0       1     2018-12-08 20:29:02     NULL

第三张表: tournament_user_answers，有所有的用户答案。

id  option_id   user_id score   active  created_at  updated_at 

此表此时没有数据。

我想实现用户没有回答的所有问题，因此查询应该返回第一个问题。下面是我尝试过的查询，但它总是返回null

SELECT * FROM tournament_user_answers 
INNER JOIN tournament_options ON tournament_user_answers.option_id = 
tournament_options.id AND tournament_options.active = 1
LEFT JOIN tournament_questions ON tournament_questions.id = 
tournament_options.question_id AND tournament_questions.active = 1 
WHERE tournament_questions.tournament_id = 5 AND  
tournament_questions.active = 1 
AND tournament_questions.id IS NULL AND tournament_user_answers.user_id = 1
LIMIT 1

Answer 1

首先是FROM tournament_user_answers (它是空的)，然后执行一个LEFT JOIN，它包含左手上的所有行(这是空的)，并在这些行上附加数据(如果可用的话)。empty LEFT JOIN data将是空的。

SELECT 
    tournament_questions.*
FROM tournament_questions
JOIN tournament_options 
    ON tournament_options.question_id = tournament_questions.id
    AND tournament_options.active = 1
LEFT JOIN tournament_user_answers 
    ON tournament_user_answers.option_id = tournament_options.id
    AND tournament_user_answers.user_id = 1
WHERE
    tournament_questions.tournament_id = 5 
    AND tournament_questions.active = 1 
GROUP BY tournament_questions.id
HAVING MAX(tournament_user_answers.id) IS NULL
ORDER BY tournament_questions.id ASC 

在这种情况下，左边的部分(问题+选项)有数据，如果可用的话，答案会附加在后面。通过在您的MAX(tournament_user_answers.id) IS NULL中包含一个HAVING，您将得到所有没有答案的问题。

Answer 2

也许这会适用于您的情况(简化版本)：

SELECT q.id, q.question, ua.id ua_id FROM tournament_questions q
INNER JOIN tournament_options o ON q.id = o.question_id
LEFT JOIN tournament_user_answers ua ON o.id = ua.option_id
GROUP BY q.id
HAVING ua_id IS NULL