MySQL的左连接与条件在右表 [英] Mysql left join with condition in right table

问题描述

一段时间以来，我一直在尝试解决此问题，希望有人能帮助我.我有两个表，第一个表是

I have been trying to solve this issue for a while, hope anyone help me. I am having two table, the first table is

表名:OnlineTest

OnlineTestId     category    subcategory                                                                   
     1            English      Spelling                                                                    
     2            English      Grammar
     3            English      Antonyms
     4            English      Synonyms

第二个表是

表名:UserStatus

Id     userId    status         onlineTestId
1       1        Finished           1
2       1        Not Finished       2
3       2        Not Finished       1
4       2        Finished           3
5       3        Not Finished       4

结果

Result

OnlineTestId    userId        status
    1               1         Finished
    2               1         Not Finished
    3               null      null
    4               null      null

我已经尝试过此查询，

select c.onlinetestid, d.userid, d.status from onlinetest c left join userstatus d on d.onlinetestid = c.onlinetestid
where c.category = 'English' and d.userid = 1;

但是此查询将带出结果的前两行，而不是最后两行，其中userId和status为null.

But this query is bring the first two row of the result and not the last two, in which the userId and status are null.

如何取得上述结果?

推荐答案

将d.userid = 1谓词放在ON子句中:

select c.onlinetestid, d.userid, d.status 
from onlinetest c 
left join userstatus d on d.onlinetestid = c.onlinetestid and d.userid = 1
where c.category = 'English' 

这将返回onlinetest中的所有行，其中userstatus的列用null填充，其中谓词d.userid = 1失败.

This will return all rows from onlinetest, having columns of userstatus filled with nulls where predicate d.userid = 1 fails.

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