MySQL查询不正确

chris3spice

我的MySQL查询有一个我无法解决的问题...

我正在尝试计算表“问题”中有多少行，这些行不在user_id匹配的表“答案”中

这是我目前的MySQL，尽管它应该返回13，但它仍返回0行。

SELECT COUNT(*) AS total 
FROM questions 
LEFT OUTER JOIN answers 
  ON questions.quest_id = answers.ans_question 
JOIN users 
  ON answers.ans_user = users.user_id 
WHERE answers.ans_question IS null 
  and questions.quest_level <= (SELECT user_level 
                                FROM users 
                                WHERE username = 'chris3spice') 
  and answers.ans_user = (SELECT user_id 
                          FROM users 
                          WHERE username = 'chris3spice');

这是我最初的查询，它返回有多少没有答案的问题……但是它没有考虑到user_id，但它确实考虑到了user_level，所以那里没有问题……

SELECT COUNT(*) AS total 
FROM questions 
LEFT OUTER JOIN answers 
  ON questions.quest_id = answers.ans_question 
LEFT OUTER JOIN users 
  ON answers.ans_user = users.user_id 
WHERE answers.ans_question IS null 
  and questions.quest_level <= (SELECT user_level 
                                FROM users 
                                WHERE username = 'chris3spice');

这是我的表格供参考

quest_id ----- quest_text ------ quest_ans

1_ _ _ _ _ _ _ _ _ _嗒嗒_ __ _ ___ _blah

...

14_ _ __ _ ____等等_ __ _ ___ _blah

。

ans_id ----- ans_user ------ ans_quest

1_ _ __ _ __ _ __ __ 1 _ __ _ ___ _1

...

14_ _ __ _ ____ 2 _ __ _ ___ _13

。

。user_id -----用户名

1_ _ __ _ ___ _chris3spice

2_ _ __ _ ___ _测试

阿格里佐

尝试一下-我挪开了一些您的逻辑以隔离LEFT JOIN

SELECT COUNT(*) AS total 
FROM users
JOIN questions
  ON questions.quest_level <= users.user_level
LEFT OUTER JOIN answers 
  ON questions.quest_id = answers.ans_question
    AND answers.ans_user = users.user_id
WHERE answers.ans_question IS null 
  AND users.username = 'chris3spice';

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。