mysql 左连接 where,MySQL的左连接与条件在右表

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

Table Name : OnlineTest

OnlineTestId category subcategory

1 English Spelling

2 English Grammar

3 English Antonyms

4 English Synonyms

The second table is

Table Name : 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

I have tried this query,

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;

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.

How to bring the above result?

解决方案

Place the d.userid = 1 predicate in the ON clause:

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'

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