mysql - 无法在 SQL 中显示正确的列表

Question

几天来我一直在处理 MySQL 查询，但我似乎无法找到解决方案。

我正在制作一种成就列表。我想要的是进行 SELECT 查询，用户可以在其中查看他的所有成就。

我的数据库是这样的:

Badges 
----------------------------------------------------------------------
| idbadges (PK) | subject_subject (FK) | description   | pathToImage |
----------------------------------------------------------------------
| 1             | (subject)            | (description) | (image)     |
| 2             | (subject)            | (description) | (image)     |
| 3             | (subject)            | (description) | (image)     |
| 4             | (subject)            | (description) | (image)     |
| 5             | (subject)            | (description) | (image)     |
| 6             | (subject)            | (description) | (image)     |
| 7             | (subject)            | (description) | (image)     |
| 8             | (subject)            | (description) | (image)     |
| 9             | (subject)            | (description) | (image)     |
----------------------------------------------------------------------

ScoreList - Many to Many relationship
--------------------------------------------------------
| users_email(FK/PK) | badges_idbadges (FK/PK) | done  | 
--------------------------------------------------------
| 1111@test.com      | 1                       | 2     | 
| 1111@test.com      | 2                       | 1     | 
| 1111@test.com      | 3                       | 0     | 
| 1111@test.com      | 4                       | 0     | 
| 2222@test.com      | 1                       | 0     | 
| 2222@test.com      | 2                       | 1     | 
| 2222@test.com      | 6                       | 2     | 
| 2222@test.com      | 7                       | 3     | 
--------------------------------------------------------

每个用户都有一个徽章列表。这些徽章有完成值，0 表示未获得徽章，上面的所有内容表示您获得的数量。如果徽章不在该用户的 scoreList 中，则它默认为 0(在程序内。不在数据库中)。

我想要的是显示用户拥有的列表。如果用户没有特定徽章(或数据不存在)，我想在其位置设置一个 NULL 值

例子:

-----------------------------------------------------------------------
|users_email  |idbadges|subject_subject|description  |pathToImage|done|
-----------------------------------------------------------------------
|1111@test.com|1       |(subject)      |(description)|(image)    |2   |
|1111@test.com|2       |(subject)      |(description)|(image)    |1   |
|1111@test.com|3       |(subject)      |(description)|(image)    |0   |
|1111@test.com|4       |(subject)      |(description)|(image)    |0   |
|NULL         |5       |(subject)      |(description)|(image)    |NULL|
|NULL         |6       |(subject)      |(description)|(image)    |NULL|
|NULL         |7       |(subject)      |(description)|(image)    |NULL|
|NULL         |8       |(subject)      |(description)|(image)    |NULL|
|NULL         |9       |(subject)      |(description)|(image)    |NULL|
-----------------------------------------------------------------------

我觉得使用 LEFT JOIN 应该很容易，但我似乎无法理解。我现在拥有的是:

SELECT 
    IF (s.users_email = '1111@test.com', s.users_email,null) AS users_email,
    b.*,
    IF (s.users_email = '1111@test.com', s.done,null) AS done
FROM
badges b
    LEFT JOIN
scoreList s ON b.idbadges = s.badges_idbadges

这几乎可以工作，但我得到的结果是:

-----------------------------------------------------------------------
|users_email  |idbadges|subject_subject|description  |pathToImage|done|
-----------------------------------------------------------------------
|NULL         |1       |(subject)      |(description)|(image)    |NULL|
|1111@test.com|1       |(subject)      |(description)|(image)    |2   |
|NULL         |2       |(subject)      |(description)|(image)    |NULL|
|1111@test.com|2       |(subject)      |(description)|(image)    |1   |
|1111@test.com|3       |(subject)      |(description)|(image)    |0   |
|1111@test.com|4       |(subject)      |(description)|(image)    |0   |
|NULL         |5       |(subject)      |(description)|(image)    |NULL|
|NULL         |6       |(subject)      |(description)|(image)    |NULL|
|NULL         |7       |(subject)      |(description)|(image)    |NULL|
|NULL         |8       |(subject)      |(description)|(image)    |NULL|
|NULL         |9       |(subject)      |(description)|(image)    |NULL|
-----------------------------------------------------------------------

那是因为它仍然读取其他用户 (2222@test.com) 的结果有谁知道如何获得示例一中的结果？如果您需要任何其他信息，只要不是个人信息，我很乐意提供。

Answer 1

SELECT [columns you want] 
from badges 
left join ScoreList on badges.idbadges = ScoreList.badges_idbadges AND 
ScoreList.users_email = '1111@test.com'