MySQL 有条件的左连接 [英] MySQL Left Join with conditional

问题描述

这看起来很简单，我有一个表问题"，其中存储了所有问题的列表，还有一个多对多表，位于问题"和用户"之间，称为question_answer".

It seems pretty simple i have a table 'question' which stores a list of all questions and a many to many table which sits between 'question' and 'user' called 'question_answer'.

是否可以进行一次查询以获取问题表中的所有问题以及用户已回答的问题，但未回答的问题为 NULL 值

Is it possible to do one query to get back all questions within questions table and the ones a user has answered with the un answered questions being NULL values

问题:

| id | question |

问题答案:

| id | question_id | answer | user_id |

我正在执行此查询，但条件是强制仅返回已回答的问题.我需要求助于嵌套选择吗?

I am doing this query, but the condition is enforcing that only the questions answered are returned. Will i need to resort to nested select?

SELECT * FROM `question` LEFT JOIN `question_answer`
ON question_answer.question_id = question.id
WHERE user_id = 14583461 GROUP BY question_id

推荐答案

如果 user_id 在外部连接到表中，那么您的谓词 user_id = 14583461 将导致不返回 user_id 为空的任何行，即具有未回答问题的行.您需要说user_id = 14583461 或 user_id 为空"

if user_id is in the outer joined to table then your predicate user_id = 14583461 will result in not returning any rows where user_id is null i.e. the rows with unanswered questions. You need to say "user_id = 14583461 or user_id is null"

这篇关于MySQL 有条件的左连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！