MySQL 查询不正确

Question

我的 MySQL 查询有问题，我无法弄清楚...

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

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

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__________废话< em>________blah

...

14_________废话___< em>_____blah

.

.

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

1__________1< em>________1

...

14_________2___< em>_____13

.

。user_id-----用户名

1_________chris3spice

2_________测试

Answer 1

试试这个 - 我移动了一些逻辑来隔离 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';