我正在研究一所在线大学，该大学拥有用户，课程和用户到课程状态。我有一个用户列表，另一个课程列表。我想查找所有给定用户和课程的课程状态，包括用户尚未开始的课程的空状态。
所以例如：
User IDs: [1, 7, 14, 21]
Course IDs: [5, 8, 36, 50]
期望的结果：
Name Course Status
John Doe How to Tie your Shoes Complete
John Doe How to Paint your House In Progress
Jane Doe How to Tie your Shoes Complete
Jane Doe How to Paint your House Not Started <-- These are the tricky ones
。
似乎我可以在表上执行 LEFT JOIN 并获得一些 NULL 值，我可以将其合并为"未开始"，但只要我添加一些约束来限制我正在寻找的课程和/或用户......它停止给我 NULL 值值，因为 NULL 课程 ID 显然不在我上面的课程列表中。
下面是一个示例查询，可让您了解我一直在尝试的内容(以及其他内容(：
SELECT
`users`.`name` AS `Name`,
`users`.`email` AS `Email`,
`courses`.`number` AS `Course #`,
`courses`.`name` AS `Course`,
COALESCE(`courses_users_statuses`.`name`, 'Not Started') AS `Status`
FROM
`users`
LEFT JOIN `courses_users`
ON `courses_users`.`user_id` = `users`.`id`
LEFT JOIN `courses`
ON `courses`.`id` = `courses_users`.`course_id`
LEFT JOIN `courses_users_statuses`
ON `courses_users_statuses`.`id` = `courses_users`.`status_id`
WHERE
`courses`.`id` IN ([1, 2, 3, 4, 5, 10, 11, 12, 16, ...])
AND `users`.`id` IN ([1, 2, 3, 4, 5, 20, 21, 36, 48, ...])
ORDER BY
`users`.`name`,
`courses`.`number`
关于如何写这样的东西的任何想法？另外，如果我可以提供更多详细信息或更多代码/表示例，请告诉我。
编辑：这是我使用以下答案的建议更新的查询：
SELECT
`users`.`name` AS `Name`,
`users`.`email` AS `Email`,
`courses`.`number` AS `Course #`,
`courses`.`name` AS `Course`,
COALESCE(`courses_users_statuses`.`name`, 'Not Started') AS `Status`
FROM
`users`
LEFT JOIN
`courses_users` ON `courses_users`.`user_id` = `users`.`id`
LEFT JOIN
`courses` ON `courses`.`id` = `courses_users`.`course_id` AND `courses`.`id` IN (1, 2, 3, 4, 5)
LEFT JOIN
`courses_users_statuses` ON `courses_users_statuses`.`id` = `courses_users`.`status_id`
WHERE
`users`.`id` IN (1, 2, 3, 4, 5)
ORDER BY
`partners`.`name`,
`users`.`name`,
`courses`.`number`
此更新的示例是一项改进，但现在它显示的记录没有课程名称或编号，但存在状态。我不确定它是如何为应该存在的课程关系争取地位的。相反，这些应该是 NULL(或"未启动"(。下面是数据库中的一些示例数据：
`users`表：
id name email
1 Stevie McComb test@example.com
2 John Doe test@example.org
3 Jane Doe test@example.net
`courses`表：
id number name
1 101 Navigation
2 102 Logging In
3 103 Updating Records
4 104 Managing Users
`courses_users`表：
course_id user_id status_id completed_at
1 1 2 2017-01-01 00:00:00
3 1 1 2017-01-05 00:23:00
1 2 2 2017-04-13 15:00:37
`courses_users_statuses`表：
id name slug
1 In Progress progress
2 Complete complete
期望的结果：
Name Email Course # Course Status
Stevie McComb test@example.com 101 Navigation Complete
Stevie McComb test@example.com 102 Logging In Not Started
Stevie McComb test@example.com 103 Updating Records In Progress
Stevie McComb test@example.com 104 Managing Users Not Started
John Doe test@example.org 101 Navigation Complete
John Doe test@example.org 102 Logging In Not Started
John Doe test@example.org 103 Updating Records Not Started
John Doe test@example.org 104 Managing Users Not Started
Jane Doe test@example.net 101 Navigation Not Started
Jane Doe test@example.net 102 Logging In Not Started
Jane Doe test@example.net 103 Updating Records Not Started
Jane Doe test@example.net 104 Managing Users Not Started
当前结果：
Name Email Course # Course Status
Stevie McComb test@example.com Complete
Stevie McComb test@example.com Not Started
Stevie McComb test@example.com 103 Updating Records In Progress
Stevie McComb test@example.com Not Started
John Doe test@example.org 101 Navigation Complete
John Doe test@example.org Not Started
John Doe test@example.org Not Started
John Doe test@example.org Not Started
Jane Doe test@example.net Not Started
Jane Doe test@example.net Not Started
Jane Doe test@example.net Not Started
Jane Doe test@example.net Not Started
问题是你把约束放在你的where子句中，而不允许一个null值。这实质上是将left join改回inner join。
要解决此问题，您可以显式允许 null，也可以将逻辑移动到 join 子句(我的首选项(。
select
`users`.`name` as `name`,
`users`.`email` as `email`,
`courses`.`number` as `course #`,
`courses`.`name` as `course`,
coalesce(`courses_users_statuses`.`name`, 'not started') as `status`
from
`users`
left join `courses_users`
on `courses_users`.`user_id` = `users`.`id`
left join `courses`
on `courses`.`id` = `courses_users`.`course_id`
and `courses`.`id` in ([1, 2, 3, 4, 5, 10, 11, 12, 16, ...])
left join `courses_users_statuses`
on `courses_users_statuses`.`id` = `courses_users`.`status_id`
where
`users`.`id` in ([1, 2, 3, 4, 5, 20, 21, 36, 48, ...])
order by
`users`.`name`,
`courses`.`number`
当我必须处理潜在的 NULL 值时，我使用
IFNULL(field_name, 'use this value instead');
例如
SELECT
IFNULL(Course, 'Nothing Found') AS course
FROM
Course
Where....
或者您可以在 WHERE 子句中指定...
WHERE Course IS NOT NULL